1 / 21

2mis6010-L Databases and Spreadsheets

2mis6010-L Databases and Spreadsheets. Lecturer Nishant Pithia. Week 2: Database Design. Lecture : a gentle introduction to database design, using last week practical exercises as an example Practical Session : Database Design with E-R Diagram. Database Design.

idania
Download Presentation

2mis6010-L Databases and Spreadsheets

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. 2mis6010-L Databases and Spreadsheets Lecturer Nishant Pithia

  2. Week 2: Database Design • Lecture: a gentle introduction to database design, using last week practical exercises as an example • Practical Session: Database Design with E-R Diagram

  3. Database Design • DB Design is the process of creating a design for a database that will • support the enterprise’s operations and objectives • and meet the enterprise’s information needs. • Standard Approaches for DB Design: • E-R Diagram • Normalization

  4. Database Design – E-R Diagram (ERD) • Entity-Relationship (ER) Modeling is a conceptual modeling technique to identify the entities and the relationship between the entities for relational database design. • Elements of ERD: • Entity: a group of real life objects (such as people, products, and so on) with the same properties. In Ms Access, an entity is used to represent a table. 2. Attribute: a property of an entity. In Ms Access, attributes are referred to those fields/columns in the tables.

  5. Elements of ERD –Primary Key and Entity Integrity Elements of ERD: • Primary Key: the attribute used to uniquely identify each occurrence of an entity. In Ms Access, PK is the key attribute as an ID to uniquely identify data for each row/entry. For every table, there must be PK(s) to identify each row of data. Entity Integrity: An Integrity Constraint of DB setting following rules • The Existence of PK value: PK can not be null value (i.e. must contain some value). A null values is an unknown value. • The Uniqueness of PK Value: the value of PK for each row must be different. Otherwise, you will violate the entity integrity of the database. For standard database design, these essential elements can be presented in E-R diagram

  6. Entity and Attribute for Last week Exercise For example, • Customer as an entity having attributes, such as No, name, address; Customer No is the PK for Customer table. • Product as an entity having attributes, such as No, name; Product No is the PK for Customer table. • Order as an entity having attributes, such as No, name; Product No is the PK for Customer table. Description name No No Name Name No Address Customer Order Product

  7. Example of Entity Integrity and PK For each entity with attributes, you will have following proposed table view in mind (not the real database).

  8. ERD Element - Relationship For relational database, We use “relationship” as an element to link different entities together instead of isolating them from one another. • Relationship: a set of meaningful association among entities. • Different types of association between entities • One-to-one • One-to-many (or Many-to-one) • Many-to-many

  9. Types of Relationship with examples • One-to-one: the head of a Department manages the department. Head manages Department Each Head can only manage one department, each department can be only managed by one head.

  10. Types of Relationship with examples • One to Many (or Many-to-one) Department Course Has Is_Run_In IT Physics Arts Msc IT Bsc IT Bsc Physics BA Arts MA Arts Each department has one or more courses; each course can be only run in a particular department.

  11. Types of Relationship with examples • Many-to-Many Department Course Has Is_Run_In IT Physics Arts Msc IT Bsc Physics and IT BA Arts With IT BA Arts Each department has one or more courses; each course can be run in more than one department.

  12. Some Tips for Database Design Step 1:Identify the entities with attributes and the Relationships among entities • Make assumptions, by clearly understanding the scenario/background information for your database design. (Different assumptions will bring your totally different DB design!!!) • Map entities with attributes and Find the “direct relationship” between different entities. (must have very clear logic) • Clearly identify the types of relationship Step 2: Use some rules to check your design Step 3: Realize the Relationships in table view • Find and create Foreign Key (thematching field) between the entities (tables) as the link to realize the relationship Step 4: Normalize the Tables.

  13. Step 1 for Last Week Exercises Entity View with Attributes Customer Product Order • Think About: Any Assumption for this example? What are the relationships among entities? Where are the direct relationships? Proposed Table View with PK Under Entity Integrity

  14. Step 1 for Last Week Exercises Under Assumption 1 One Customer has one or more orders; each order can only belong to a particular customer (different customers’ names can not appear in the same order) An Order can have more than one product; each product can be only belong to a particular customer’s order (bespoken or personalized products. e.g. Rolls Royce; designer branded fashion for celebrities) Assumption 1 D021 B001 M746 B111 A222 C122 U120 U129 Anderson Cooper King O214 O315 O266 O478 O122 Conceptual View ER View Order Customer Product

  15. Step 1 for Last Week Exercises Under Assumption 2 One Customer has one or more orders; each order can only belong to a particular customer (different customers’ names can not appear in the same order) One Order can contain more than one product; each product can appear in different orders. (e.g. consumer products. e.g. clothes in shopping centre; books, etc… ) Assumption 2 D021 B001 B111 C122 U120 U129 Anderson Cooper King O214 O315 O266 O478 O122 Conceptual View ER View Customer Order Product

  16. Step 2: Some General Rules for Good DB Design For multiple 1:M (or M:1) relationships • Try to avoid such fan shape connection for database design in ER View Customer Order Product Problem: What are the products in the order?? • Fan shape “trap” will exist when the direct relationship is incorrectly identified In our example (Slide 14 & 15), under different assumptions, both DB Design in ER View do not have fan shape.

  17. Step 3 –Finding and CreateForeign KeyforRelationship in Table View • Foreign Key (FK) isan attribute/a field of one table and a primary key of another table. It is the matching field between interrelated tables. • The use of FK introduces another new integrity constraint - Referential Integrity (or FK constraint). • It simply sets a rule that: if a FK exists in a table, the value of the FK must match the value of the primary key of some row in another table.

  18. Example of FK and RI • Which is the PK to be created as a FK in another table for relationship? Course_Code or Dept_ID? • Dept_ID (the PK of department) in Course table as the FK to link • and represent the relationship between them.

  19. Step 3 for Last Week Exercise- Under Assumption 1 Violate Entity Integrity (No Duplication for PK value)

  20. Step 3 Finding FK forRelationship– Under Assumption 1

  21. Answer from Assumption 1

More Related