1 / 40

Database Logical Design

Database Logical Design. Overview. Logical models Transforming ERD to relational models. Relational Model. Relational model is a logical model Specific mathematical theories and rules Two ways to design a relational model Through normalization From conceptual models (ER).

garnerj
Download Presentation

Database Logical Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Logical Design

  2. Overview • Logical models • Transforming ERD to relational models

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

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

  5. E-R Diagrams • They provide a way to pictorially depict the entities, attributes and relationships. • These are also called semantic networks. • There are three elements of the ER-Diagram • 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

  6. AuName Title ISBN Price AuID AuPhone Written By Books Authors Published By Is A Publishers Contributor PubID PubPhone ConID ConLevel PubName ConType E-R Diagram: Book Database

  7. E-R Diagrams • Among book authors there are people who are not primary authors but are contributors. • e.g. illustrators, indexers etc. • Each has a different level based on the contribution • A separate entity can be used to represent contributors • Attributes: Level and Type. • Let us now define the relationships. • 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 model.

  8. 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)

  9. E-R Diagram: Interior Designer CustName Date Time SeminarID CustID CustPhone CustAddr M N Attended By Location Seminar Customer 1 CustEmail M Title Buys Requests N N ProdPrice Consulting Product ConRate ProdQty ConID ConType ProdID ProdType ConHours ProdName

  10. 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 ITEM-TYPE 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.

  11. E-R Diagram: Supplier ProdName SupStreet SupCity ProdID ProdDesc SupPhone SupZip ProdType Address Items M N Has 1 1 N N Supplies SupID Supplier SupName SupPhone

  12. E-R Diagram: Supplier ProdName SupStreet SupCity ProdID ProdDesc SupZip ProdType Address Items N 1 Has Procured Date 1 N N Supplies SupID Supplier Purchases Price SupName SupID ProdID SupPhone Quantity

  13. 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.

  14. E-R Diagram: Hospital DName PLName PFName PSex PSSN DID AuPhone Admitted By PCode Patients Departments Get Is A Treatments Contributor TID T ConID ConLevel TReact ConType

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

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

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

  18. Weak Entities • Weak entity’s primary key is a composite key including its owning table’s primary key • Example: • Building(BuildingID, Location) • Room(BuildingID, RoomNumber, RoomType)FK: BuildingID  Building.BuildingID

  19. 1:N 1:N 1:N Dependent Apartment Student Employee Building Advisor Weak Entities • Weak entities are those that can not exist unless another entity also exists in the database • Entity that is not weak is a strong entity The employee can exist without a dependent but not vice versa 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 Even though a business rule says that each student should have an advisor student is still a strong entity

  20. Weak Entities 1:N 1:N Order Prescription SalesPerson Patient • 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 • Prescription can not logically exist without a patient thus it is a weak entity • Thus a weak entity is the one with a cardinality (minimum cardinality) of 1 and a logical dependence on another entity

  21. Representing Relationships • Designing foreign keys • 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?

  22. Locker Employee LockerID EmpID Size LockerID Foreign Key? EmpName AssignedTo Foreign Key? FK in 1:1 Relationships • The key from one relation is placed in the other as a foreign key • Which table?

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

  24. Considering Minimum Cardinality • When only one side is optional, foreign key is placed on the optional side Locker Employee has Remove AssignedTo LockerId as FK Locker Employee has AssignedTo as FK Remove LockerId

  25. Considering Minimum Cardinality • When both sides are optional • Put the foreign key on the side which causes minimum null values • Create an intersection table: Locker (LockerId, Size, Location)Employee (EmpId, Fname, Lname)LockerAssignment (LockerId, EmpId) Locker Employee has

  26. FK in 1:N Relationships • One-to-Many • 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 TaughtBy

  27. Department Employee DeptID EmpID Primary Key DeptName Dept Foreign Key EmpName Location FK in 1:N Relationships • Another example Department Employee has

  28. Transforming N:M Relationships • Many-to-Many • 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 Parts have Orders OrderLines Parts

  29. Transforming N:M Example • To represent an M:N relationship, an intersection table is created • 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

  30. Design Consideration 2 • Adding a surrogate key? • Intersection tables • Weak entity tables

  31. Exercise 1: M:N Relationship In Character

  32. Transforming Unary/Recursive Relationships • Use a foreign key column to refer back to its primary key • Exmaple: • Customer(CustomerId, Name, Referredby) FK: ReferredBy  Customer.CustomerId • To avoid null values, a separate table can be created • Customer(CustomerId, Name, Referredby)CustomerRefer(CustomerId, Referredby) FK: CustomerId Customer.CustomerIdFK: ReferredBy  Customer.CustomerId Customer Refers

  33. 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, …)NotebookUse(UseId,TID, PID, NID)

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

  35. Exercise • Creating tables and keys

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

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

More Related