Database Logical Design Overview Logical models Transforming ERD

Database Logical Design

Overview Logical models Transforming ERD to relational models 2

Relational Model Relational model is a logical model n Specific mathematical theories and rules Two ways to design a relational model n n Through normalization From conceptual models (ER)

Transforming ER to Relations 1. Transforming basic entities, identifiers and attributes n n Creating tables, columns and keys Defining the data type, length and constraints for each columns 2. Transforming relationships by determining foreign keys n n Which column is foreign key? Which table to add a foreign key? 4

E-R Diagrams They provide a way to pictorially depict the entities, attributes and relationships. n These are also called semantic networks. There are three elements of the ER-Diagram n n Entities are represented by labeled rectangles. The label is the name of the entity. Attributes are represented by oval boxes and contain the name of the entity Relationships are represented by a diamond connected to the two entities using solid lines (cardinality of many is represented by an infinity sign, cardinality of 1 is represented by a 1) Weak entities are represented by a rectangle curved at the corners and the relationship triangle curved at the corners

E-R Diagram: Book Database Au. Name Title ISBN Price Books Au. ID Written By Au. Phone Authors Published By Is A Publishers Contributor Pub. ID Pub. Phone Pub. Name Con. ID Con. Level Con. Type

E-R Diagrams Among book authors there are people who are not primary authors but are contributors. n n e. g. illustrators, indexers etc. Each has a different level based on the contribution A separate entity can be used to represent contributors n Attributes: Level and Type. Let us now define the relationships. n n n A Book is written by authors A Book is published by a publisher A Contributor is an author Once this semantic model is created we need to create a relational database with this semantic

Example An interior designers who specializes in home kitchen designs offers a variety of seminars at home shows, kitchen and appliance stores, and other public locations. The seminars are free; she offers them as a way of building her customer base. She earns revenue by selling books and videos and instructs people on kitchen design. She also offers custom-design consulting services. Her business is in selling products to the attendees at her seminars. She would like to develop a database to keep track of customers, the seminars that they have attended, and the purchases that they have made. Please determine the entities, attributes and relationships that should exist in the database and draw an E-R diagram. (Source: Database Concepts by Kroenke)

E-R Diagram: Interior Designer Cust. Name Date Time Location Seminar. ID Seminar M Cust. ID N Customer 1 M Attended By Title Con. Rate Con. ID Con. Type Con. Hours Cust. Addr Cust. Email Buys Requests N Consulting Cust. Phone Prod. Qty N Product Prod. ID Prod. Price Prod. Type Prod. Name

E-R Diagrams An organization purchases items from a number of suppliers. It keeps track of the items purchased from each supplier, and it also keeps a record of suppliers' addresses. Items are identified by ITEMTYPE and have a DESCRIPTION. There may be more than one such address for each supplier, and the price charged by each supplier for each item is stored. Suppliers are identified by SUPPLIER-ID.

E-R Diagram: Supplier Sup. Street Sup. City Sup. Zip Sup. Phone Address N Prod. Name Prod. ID Prod. Desc Items M Has Sup. ID 1 Supplier N Sup. Name Sup. Phone Supplies Prod. Type

E-R Diagram: Supplier Prod. Name Sup. Street Sup. City Sup. ID Prod. ID Sup. Zip Prod. Desc Address N Items 1 Has Procured 1 Supplier N Sup. Name Sup. Phone Supplies Prod. Type N Purchases Sup. ID Prod. ID Quantity Date Price

E-R Diagrams A hospital stores data about patients, their admission and discharge from departments and their treatments, For each patient, we know the name, address, sex, social security number, and insurance code (if existing). For each department, we know the department's name, its location, the name of the doctor who heads it, the number of beds available, and the number of beds occupied. Each patient gets admitted at a given date and discharged at a given date. Each patient goes through multiple treatments during hospitalization; for each treatment, we store its name, duration, and the possible reactions to it that the patient may have.

E-R Diagram: Hospital PLName DName PFName PSex PSSN Patients PCode Admitted By DID Au. Phone Departments Get Is A Treatments Contributor TID T TReact Con. ID Con. Level Con. Type

Basic Tables and Columns Each entity becomes a relation (table) Attributes of the entity become fields of that table Identifier becomes primary key 18

Column Definition Column name Data type n n Numeric: integer, float/decimal Character: string/text, fixed vs. varied length Date Boolean/bit Column length Constraints n n Key Null //required or not

Derived Attribute Derived attribute n n An attribute that is generated based on other columns, or rows GPA, Total. Payment, etc. Derived attributes are usually not created at design time

Weak Entities Weak entity’s primary key is a composite key including its owning table’s primary key Example: n n Building(Building. ID, Location) Room(Building. ID, Room. Number, Room. Type) FK: Building. ID

Weak Entities Weak entities are those that can not exist unless another entity also exists in the database n Entity that is not weak is a strong entity Employee 1: N Dependent The employee can exist without a dependent but not vice versa Building 1: N Apartment In this case the apartment address is a composite of building number and appartment number, so apartment can’t exist without building. (Such entities are also called id-dependent entities) The entity should not only depend physically but also logically to avoid ambiguities Advisor 1: N Student Even though a business rule says that each student should have an advisor student is still a strong entity

Weak Entities n By business rule order would have a sales person associated with it but this is not a logical necessity this order is not a weak entity Sales. Person n Order Prescription can not logically exist without a patient thus it is a weak entity Patient n 1: N Prescription Thus a weak entity is the one with a cardinality (minimum cardinality) of 1 and a logical dependence on another entity

Representing Relationships Designing foreign keys n n Which column is foreign key (and referencing which table)? In case when a foreign key is not present: which table should it be added to?

FK in 1: 1 Relationships The key from one relation is placed in the other as a foreign key n Which table? Locker Employee Locker. ID Emp. ID Size Assigned. To Foreign Key? Locker. ID Emp. Name 25

Considering Minimum Cardinality When both side are mandatory (minimum cardinality is 1), Foreign key is placed on either side State has Governor Can we combine two tables into one table?

Considering Minimum Cardinality When only one side is optional, foreign key is placed on the optional side Locker has Remove Assigned. To Locker Assigned. To as FK Employee Locker. Id as FK has Employee Remove Locker. Id

Considering Minimum Cardinality When both sides are optional n Put the foreign key on the side which causes minimum null values Locker n has Employee Create an intersection table: Locker (Locker. Id, Size, Location) Employee (Emp. Id, Fname, Lname) Locker. Assignment (Locker. Id, Emp. Id)

FK in 1: N Relationships One-to-Many n n The primary key from the “One” side is placed in the “Many” side as a foreign key The foreign key is always on the “Many” side Instructor Course. Section Instructor. ID CRN First. Name Semester Office Course. ID Department Time Taught. By 29

FK in 1: N Relationships Another example Department has Employee Department Dept. ID Dept. Name Location Employee Emp. ID Primary Key Foreign Key Dept Emp. Name 30

Transforming N: M Relationships Many-to-Many n n There is no direct way to map many-to-many relationships in relational database A Many-to-Many relationship can also be modeled as two One-to-Many relationships Orders have Parts Order. Lines Parts 31

Transforming N: M Example Orders Order. Lines Parts Order. Num (PK) Order. Num (FK) Part. Num (PK) Order. Date Part. Num (FK) Description Customer. Num. Ordered Class Price To represent an M: N relationship, an intersection table is created n n n The primary key is a composite key consisting of both primary keys from other two tables Foreign keys are added to the intersection table referencing corresponding tables If there are more attributes for the relationship, add them to the intersection table as columns 32

Design Consideration 2 Adding a surrogate key? n n Intersection tables Weak entity tables

Exercise 1: M: N Relationship Actor Movie Actor. Id First. Name Last. Name Born. Date Movie. Id Title Type Made. By In Character 34

Transforming Unary/Recursive Relationships Use a foreign key column to refer back to its primary key Customer Exmaple: n Refers Customer(Customer. Id, Name, Referredby) FK: Referred. By Customer. Id To avoid null values, a separate table can be created n Customer(Customer. Id, Name, Referredby) Customer. Refer(Customer. Id, Referredby) FK: Customer. Id FK: Referred. By Customer. Id

Transforming Ternary Relationship The relationship turns into a table and has three foreign keys referencing other tables Technician (TID, Name, Phone, …) Project(PID, Name, Budget, …) Notebook(NID, Model, Cost, …) Notebook. Use(Use. Id, TID, PID, NID)

Super- and Sub-types The identifier of the super type becomes the primary key and the foreign key of each subtype

Exercise Creating tables and keys

Data Dictionary Table description n Name Description Entity mapped Column definition n n Name and description Key definition (PK, FK and referential constraints) Data type and length Value range (check), nullable, uniqueness Design decisions and justifications

Normalization and De-normalization Normalize or de-normalize tables according to requirements and implementation considerations