Week 2

• Entities


  Real-world objects or concepts of importance to a user or an organization that need to be represented in a database
• Relationships


  Connections between entities
• Levels of database design

  • Conceptual
  • Logical
  • Physical
• What you need to know

  • Different levels of design
  • Entities
  • Relationships (between entities)
• Entity


  In the database world, an entity is a real-world object or concept of importance to a user or an organization and it needs to be represented in a database. Each entity represents one theme, one topic, or a business concept. In a relational DBMS, each entity is the 'Name' of a Table, and each row in a table is an instance of an entity. In the Entity-Relationship model, each entity is the 'Title' or 'Name' of a table.
• Entities


  • Lecturers
  • Modules
  • Students
• Each row in a table is an instance of an entity
• Relation


  A 2-D (Rows and Columns) table that has specific characteristics: Rows to contain data about instances of an entity (no two rows can be identical), Cells to contain only a single value, Columns to contain data about the attributes of the entity (each column has a unique name and all values in a single column have to be of the same data type)

  
• A non-relation table has rows that can contain more than one value in a cell
• Common database terms


  • Table
  • Row
  • Column
  • Entity
  • Record
  • Field
  • Relation
  • Tuple
  • Attribute

  
• 3 levels of database design


  • Conceptual
  • Logical
  • Physical
• Conceptual data model and its Entity-Relationship (ER) diagrams are used for database design
• Database schema is the design/structure of the data, and how it is organised
• DBMS is the software used to store the data, ideally in an efficient and robust manner
• Query language is the language used to manipulate the data stored within a DBMS, e.g. SQL
• The requirements stage gathers the requirements to build the database and transforms them into an Entity-Relationship (E-R) data model
• The conceptual-level schema is a high-level description of the database and how it is organized
• Identifying entities involves thinking about the different data needed and how they are related to each other
• Entities that might be identified for a university


  • Students
  • Modules
  • Staff
  • Assessments
  • Results
• Conceptual Level


  A high-level description of our database, and how it is organised
• Conceptual-level schema


  • Identify the data needed
  • Identify how the data are related to each other
• Important to understand each business description
• How to Identify the data (Entities) from a Business Specification

  1. Think about a shop selling goods

  2. Identify the different products available to customers

  3. Identify the shop as a location

  4. Identify sale as an event

  5. Identify a customer approaching the vendor, placing an order and receiving goods
• Entities identified in a shop


  • Products
  • Location
  • Sale
• How to Identify the data (Entities) from a Business Specification


  1. Think about a university

  2. Identify students

  3. Identify modules

  4. Identify staff

  5. Identify assessments

  6. Identify results
• Entities identified in a university


  • Students
  • Modules
  • Staff
  • Assessments
  • Results
• How to Identify the data (Entities) from a Business Specification


  1. Think about a Halifax Bank with customers and employees who drive to work

  2. Identify parking spaces

  3. Identify customers

  4. Identify bank branches

  5. Identify staff
• Entities identified in a Halifax Bank


  • Parking spaces
  • Customers
  • Bank branches
  • Staff
• Relationship


  The connection between entities
• Entity-Relationship Diagram (ERD)


  Helps conceptualise the structure of a database
• Degree of a Relationship Set

  • Defines the number of entities that participate in the relationship
  • Unary relationship (1 entity set)
  • Binary relationship (2 entity sets)
  • Ternary relationship (3 entity sets)
• Cardinality of a Binary Relationship

  • Represents the maximum number of entities that can be involved in a particular relationship
  • One-to-One (1-1)
  • One-to-Many (1-M)
  • Many-to-Many (M-N)
• Cardinality of a relationship is represented by symbols at the end of the connecting lines in an ERD
• Maximum Cardinality


  The maximum number of entities that can be involved in a particular relationship
• Minimum Cardinality


  The minimum number of entities that must be involved in a particular relationship
• Minimum cardinality is usually a value of Zero or One
• How to Identify the relationships between Entities from specifications

  1. Think about whether there is a relationship

  2. Determine the type of relationship

  3. Represent the entities and their relationships in an Entity Relationship Diagram (ERD)
• Between entities Customers and Orders


  • A customer can have zero or many orders
  • Each order must belong to a customer
• Between entities Shipments and Orders


  • Each order can have no shipment (cancellation of order) or many shipments (goods that make more than 1 delivery)
  • Each shipment must belong to an order
• Between entities Branch and Staff


  • A branch can have zero or many staff
  • Each staff must belong to a branch