Design Levels ER Models & Relational Databases References Paolo Atzeni, Stefano Ceri, Stefano Paraboschi, Riccardo Torlone: “Database systems”, McGraw-Hill 1999 R. Elmasri, S.B. Navathe: Fundamentals of Database Systems, Benjamin-Cummings, 2007 Daniele Braga, Marco Brambilla, Alessandro Campi “Eserciziario di Basi di dati”, Esculapio, 2009 Sep 2024

Introduction

The first level of knowledge you need to run a business is to know how to storage data.

Design Levels

Conceptual database design - ER Model Constructing an information model , independent from all physical consideration for an enterprise Entities Relationships Attributes (simple, no composite, no derived) Attribute domains Key attributes Logical database design - Relational Model Building an organization database based on a specific data model Physical database design Implementing a database using specific data storage structure (s) and access methods

ER Model

Introduction

It can express the overall logical structure of a database graphically. Rectangles Ellipse Diamonds Lines Double Ellipses Dashed Ellipses Double Lines Double Rectangles

Basics

Entity: Real-world object distinguishable from other objects. An entity is described using a set of attributes (properties that describe entities). Entity Set: A collection of entities of the same kind. E.g., all employees. Same Entity Set = Same Attributes = key Each Attribute has a Domain. Relationship: Association among two or more entities. Relationship Set: Collection of similar relationships. An n-ary relationship set R relates n entity sets ; each relationship in R involves entities Same Entity Set = Different Relationship Relationship can have descriptive attributes. A relationship is uniquely identified by participating entities without reference to descriptive attributes.

Key Constraints - Cardinality

Constraints are IMPORTANT because they must be ENFORCED when IMPLEMENTING the database: 1-to- 1-to Many Many-to- Many-to-Many ISA (’is a’) hierarchy: used when an entity set can be divided into subclasses that: inherit its descriptive attributes participate in additional specific relationships or have extra attributes. Reasons to use it: To add descriptive attributes specific to a subclass. To identify entities that participate in a relationship. Constraints : Overlap constraint allows entities to belong to multiple subclasses. Covering constraint requires that all superclass entities be included in one or more subclasses. Relevant Issues Redundancy occurs when the same piece of information is stored in multiple places in the database or data model unnecessarily. Misleading occurs when the data model implies incorrect relationships or semantics (gives the wrong impression about how the data behaves).

Top-down strategy

Bottom-up strategy

Relational Model Based on the mathematical notion of relation

Mathematical relations

Key : a set of attributes that uniquely identifies tuples in a relation. Superkey VS Minimal Key a set of attributes K is a superkey for a relation r if r does not contain two distinct tuples ; uniqueness + more than one column is a key for r if K is a minimal superkey (that is, there exists no other superkey K’ of r that is contained in K as proper subset) uniqueness + only one column Primary VS Foreign Key ER to Relational Databases - Relationship Conversion Relational Databases are the backbone of every organization.

Redundancy and Load

are determinants about whether each relationship in the conceptual model should be represented as a separate table and as a posted foreign key Redundancy = one fact in multiple places or multiple facts in one place Load = the percentage of non-null values in a column Participation Cardinalities communicate some of the information regarding redundancy and load

Maximum VS Minimum Cardinality

Maximum Cardinality: how many times one entity can appear related to the other. Place foreign key in the table on the 1 side of the relationship - Avoid redundancy Minimum Cardinality: if the relationship is mandatory (=1) or optional (=0) Place foreign key in the table where participation is mandatory. - Avoid null values.

5 Steps from ER to Relational

Step 1: Convert Entities to Tables Create a separate table to represent each entity in the conceptual model 1A : Each attribute of the entity becomes a column in the relational table 1B : Each instance of the entity set will become a row in the relational table Step 2: Handle Many-to-Many (M:N) Relationships Create a separate table to represent each many-to many relationship in the conceptual model: Include the primary keys of the two related entities as its own attributes. These keys together form a composite primary key for the relationship table. Step 3: Handle (1,1)-(1,1) Relationships For participation cardinality pattern (1,1)-(1,1), consider whether the two entities are conceptually Combined Merge the attributes into a single table No foreign keys or relationship table needed Separate 3A : Add the primary key from one entity's table as a foreign key in the other entity's table (once!) 3B : It doesn’t matter which entity’s primary key is posted into the other entity’s table, but DO NOT post both directions

DO NOT make a separate table Redundancy is automatically avoided and load is not an issue when you post a foreign key into either table in a (1,1)-(1,1) relationship Step 4: Handle (1,1) with (1,N) or (0,N) For remaining relationships that have (1,1) participation by one entity set, Post the related entity’s primary key into the (1,1) entity’s table as a foreign key i.e., for the following participation cardinality patterns (0,N)-(1,1) (1,N)-(1,1) (1,1)-(0,N) (1,1)-(1,N) (0,1)-(1,1) (1,1)-(0,1) Do NOT make a separate table Post a foreign key INTO the (1,1) entity’s table from the other entity’s table Redundancy is avoided and load is not an issue if you follow this instruction If you post the opposite direction, either redundancy [for N maximums] OR load [for 0 minimums] will be a problem Step 5: Handle (0,1) participation cases For remaining relationships that have (0,1) participation by one or both of the entities, consider load i.e., for the following participation cardinality patterns (0,N)-(0,1) (1,N)-(0,1) (0,1)-(0,N) (0,1)-(1,N) (0,1)-(0,1) 5A :If there is high load (many matching rows), post FK into the (0,1) table. Accept some nulls to avoid making a whole extra table. 5B : If there is low load (only a few matches), create a separate table instead. Saves space and avoids many nulls. For (0,1)-(0,1): Post in whichever direction has higher load. If neither has high load → create a separate table. Rule for maximum cards Not posting into (N) side It causes redundancy Not posting into (0,1) Since there are optionals, few matches can cause nulls Posting into (0,1) Only if it saves more space than a separate table In-Class Exercise -