A Demo of Logical Database Design

1. A Demo of Logical Database Design

2. Aim of the demo • To develop an understanding of the logical view of data and the importance of the relational model • To consider entities, attributes, and tables within the relational view • Learn basic and advanced ER modelling techniques

3. Entities • Relational database design starts by defining the required entities • Entities are anything you want to store data about • people (students, customers, employees, etc.) • places (resorts, cities, countries, etc.) • things (restaurants, products, invoices, movies, paintings, books, buildings, contracts, etc.) • events (elections, presentations, earthquakes, hurricanes, etc.)

4. Entity Sets / Entity Types • Entity sets are collections of related entities. Entities are related by their classification: • student entities are related by the fact that they are all students • invoice entities are related by the fact that they are all invoices • car entities are related by the fact that they are all cars

5. Entity Sets/Types and Entities • Unfortunately, database designers almost always use the two terms as synonyms. • When database designers refer to entities, they really are referring to entity sets or entity types. • Therefore, when you see a reference to an EMPLOYEE “entity” in a database design, remember that EMPLOYEE actually represents an entity set/type that contains a collection of employee entities and each instance of this type is one single entity. • Entity Set (type) = Class • Entity = Object

6. Attributes • Properties of entities or relationships • This is the data stored about an entity • E.g. student entity could have attributes such as name, address, DOB etc.

7. Types of attributes • Simple – each entity has a single atomic value for the attribute. Cannot be subdivided • Age, sex, marital status • Composite (composed of several components): Can be subdivided into additional attributes • Address into street, city, zip • Name(FirstName, MiddleName, LastName) • Single-valued: Can have only a single value • Person has one social security number • Multi-valued: an entity may have multiple values for that attribute • Person may have several college degrees (qualifications, skills) • Color of a car • Derived: Can be derived with algorithm • Age can be derived from date of birth

8. Overview of Database Design Conceptual design: (ER Model is used at this stage.) • What are the entities and relationships in the database? • What information about these entities and relationships should we store in the database? • What are the integrity constraints or business rules that hold? • A database `schema’ in the ER Model can be represented pictorially (ER diagrams). • Can map an ER diagram into a relational schema by hands or tools.

9. ER Diagram: Two Most Commonly Used Notations

10. ER Diagrams - The Basics

11. ER Diagram Basics - Cardinalities

12. Classification of Cardinalities • Minimum cardinality - relationship participation • Mandatory: (existence dependent) Entity occurrence requires corresponding occurrence in the related entity. Offering cannot exist without being related to a course. • Optional: Entity occurrence does not require a corresponding occurrence in the related entity. A course does not necessarily need an offering to exist. • Maximum cardinality based • 1-M • M-N • 1-1

13. Weak Entities – Identifying Relationship • Identifying relationship • One entity is existence-dependent on another • PK of related entity contains PK component of parent entity

14. Weak Entities • A weak entity is an entity that • Is existence-dependent i.e. weak entities cannot exist without the owner entity AND • Has a primary key that is partially or totally derived from the parent entity in the relationship. They do not have the PK of their own • However not every existence dependency results in a weak entity type. Eg – Driver License cannot exist unless it is related to PERSON entity even though it has its own key (Licence number) and hence is not a weak entity.

15. M-N Relationships with Attributes • Replace M-N relationship with • Associative entity type • Two identifying 1-M relationships

16. Associative Entity Type Example

17. Recursive Entities • A recursive entity is one in which a relationship can exist between occurrences of the same entity set. • A recursive entity is found within a unary relationship.

18. Entity Supertypes and Subtypes • Generalization hierarchy • Depicts relationships between higher-level supertype and lower-level subtype entities • Supertype has shared attributes • Subtypes have unique attributes • A subtype entity inherits its attributes and its relationships from the supertype entity • The supertype and its subtype(s) maintain an is-a relationship

19. Generalization Hierarchies

20. Disjointness and Completeness of Subtypes • Disjointness: D means intersection is empty • Overlap: No symbol means intersection is not empty: Faculty and Student. Subtypes have common entities • Completeness: C means union of subtype entities is the set of supertype entities • Nothing: supertype can have free standing entities ((not in any subtype). Union does not provide supertype

21. Entity Vs Attribute • Should addressbe an attribute of Employees or an entity (connected to Employees by a relationship)? • Depends upon the use we want to make of address information, and the semantics of the data: • If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued). • If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modelled as an entity (since attribute values are atomic).

22. Summary • A data model is the relatively simple representation, usually graphic, of complex real-world data structures. It represents data structures and their characteristics, relations, constraints, and transformations. • Different type attributes have different implications in a database. Some of these need to be considered carefully.

23. Comprehensive Example