1 / 55

APPENDIX C DESIGNING DATABASES {Edited by Yüe “Jeff” Zhang, CSUN}

APPENDIX C DESIGNING DATABASES {Edited by Yüe “Jeff” Zhang, CSUN}. LEARNING OUTCOMES. Describe the purpose of the relational database model in a database management system List the relational database model’s basic components

anthea
Download Presentation

APPENDIX C DESIGNING DATABASES {Edited by Yüe “Jeff” Zhang, CSUN}

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. APPENDIX C DESIGNING DATABASES {Edited by Yüe “Jeff” Zhang, CSUN}

  2. LEARNING OUTCOMES • Describe the purpose of the relational database model in a database management system • List the relational database model’s basic components • Describe the way entities and attributes are organized in a database • Familiarized with entities and attributes with real-world examples

  3. LEARNING OUTCOMES • Explain the need for an entity-relationship diagram in a database management system • Describe the Chen model symbols used in entity-relationship modeling • Understand types of relationships (1 to many etc); understand relationships among tables, primary key-foreign key • Three operations in relational database

  4. INTRODUCTION • The core chapters introduced: • Database-maintains information about various types of objects (products), people (employees), events (transactions), and places/organizations (warehouses/companies) 【see also C-7】 • Database management system (DBMS) – creates, reads (“retrieves”), updates, and deletes data in a database while controlling access and security • Relational database model - a type of database that stores its information in the form of logically-relatedtwo-dimensional tables

  5. Data Hierarchy (Figure from Laudon & Laudon) High Low Bad exmpl - A principle of DB design: smlst elmt… C-13

  6. ENTITIES AND DATA RELATIONSHIPS • Data model – The logical data structures that detail the relationships among data elements using graphics or pictures • The underlying relationships in a database environment: • Independent of the data model • Independent of the DBMS that is being used • Entity-relationship diagram (ERD) - A technique for documenting the relationships between entities in a database environment 【Beginning from C-19】 • Entity:    • Attribute:   

  7. ENTITIES AND THEIR ATTRIBUTES • Entity - Also called a table, stores information about a type of things, persons, places/orgs, transactions, or events – Discussion: C8 • Entity instance – a member in an entity: C9~C10 • Attribute – Data elements associated with an entity; describe/define the entity from aspects related to business context: C8~C18 • Think: an example for each type of entity above A CUSTOMER entity can be described by a Customer Number, First Name, Last Name, Street, City, State, Zip Code, Phone Number {Think - 1, “Address”? 2, Zip}

  8. DISCUSSION • Database-maintains information about various types of objects, places, people, and events Think: City names in State table? Similarly: UNIV, COLLEGE, FACULTY, STUDENT

  9. ENTITIES AND THEIR ATTRIBUTES;{also: entity instances} {Edited & revised by Zhang}

  10. ENTITIES AND entity instances {Edited & revised by Zhang}

  11. Entities, Attributes, and Records Fields Records; instances of the entity 4345 02/09/01 1765 8 13.75 Value of attributes / fields {Edited by Zhang}

  12. Type of ATTRIBUTES • There are several types of attributes including: • Simple versus composite • Single-valued versus multi-valued • Stored versus derived • Null-valued • Some attributes canNOT be null • Such as?

  13. 1. SIMPLE VERSUS COMPOSITE • Composite attributes can be divided into smaller subparts, which represent more basic attributes that have their own meanings • Example:Address • Address can be broken down into a number of subparts, such as Street, City, State, Zip Code • Street may be further broken down by Number, Street Name, and Apartment/Unit Number • Attributes that are not divisible into subparts are called simple attributes Refer back to C-5

  14. SIMPLE VERSUS COMPOSITE

  15. 2. SINGLE-VALUED VERSUS MULTI-VALUED • Single-valued attribute means having only a single value of each attribute of an entity at any given time • Example: • A CUSTOMER entity allows only one Telephone Number for each CUSTOMER • If a CUSTOMER has more than one Phone Number and wants them all included in the database the CUSTOMER entity cannot handle them

  16. SINGLE-VALUED VERSUS MULTI-VALUED • Multi-valued attribute means having the potential to contain more than one value for an attribute at any given time • Relational databases do not allow multi-valued attributes because they can cause problems: • Confuses the meaning of data in the database • Significantly slow down searching • Place unnecessary restrictions on the amount of data that can be stored How to handle N phone numbers?

  17. 3. STORED VERSUS DERIVED • If an attribute can be calculated using the value of another attribute, it is called a derived attribute • The attribute that is used to derive the attribute is called a stored attribute • Derived attributes are not stored in the file, but can be derived when needed from the stored attributes • Example: A person’s age – 【think how】

  18. 4. NULL-VALUED • Null-valued attribute – Assigned to an attribute when no other value applies or when a value is unknown • Example: A person who does not have a cell phone - Cell Phone Number value? • More practical use of null value: • Sold date of properties – what if null? • Transaction price of items being auctioned • Returned date of library books – what if null?

  19. DOCUMENTING ENTITY-RELATIONSHIP DIAGRAMS • Most commonly used styles of ERD notation: 1, Chen; 2, Information Engineering (Crow’s foot) • The Chen model uses rectangles to represent entities • Each entity's name appears in the rectangle and is expressed in the singular, as in CUSTOMER • Attributes are expressed in ovals • Relationships are expressed w diamonds

  20. BASIC DATA RELATIONSHIPS- Chen model

  21. BASIC DATA RELATIONSHIPS • The relationships that are stored in a database are between instances of entities – “any ONE member of entity A…”   

  22. BASIC DATA RELATIONSHIPS • Once the basic entities and attributes have been defined, the next task is to identify the relationships among entities • There are three basic types of relationships: • One-to-one • One-to-many • Many-to-many Must be read from entity A to entity B, AND then from B to A, to decide. Only examining one direction cannot determine

  23. DOCUMENTING RELATIONSHIPS – THE CHEN METHOD • The Chen method uses diamonds for relationships and lines with arrows to show the type of relationship between entities

  24. DOCUMENTING RELATIONSHIPS – THE Info Engineering METHOD • The Info Engineeringmethod uses lines for relationships, and “crow’s foot” to show the type of cardinality (1 vs many) between entities Many

  25. ONE-TO-ONE • One-to-one (1:1) – A relationship between two entities in which an instance of entity A can be related to only one instance of entity B; and entity B can be related to only one instance of entity A Chen Has (1) Located-in (1) Info Engi TOWN Has AIRPORT

  26. CAR OWNER Data Relationships (Zhang, edited) Entity-Relationship Diagram Relationships areassociationsbetweendatabase entities L-NAME ID F-NAME VIN MAKE ID ADDR owns M 1 MODEL (and more) YEAR (and more) This entity relationship represents the statement: "each owner may own many cars [left-to-right]; each car is owned by one owner [right-to-left]"

  27. ONE-TO-MANY • One-to-many (1:M) – A relationship between two entities, in which an instance of entity A (any instance of A), can be related to zero, one, or more instances of entity B; andan instance of entity B can be related to only one instance of entity A Has (many) Is-place-by (1)

  28. How many suppliers are there? Info Engring (Crow’s foot) Each supplier can supply many parts; Each part is supplied by Only one supplier SUPPLIER DEPT 1 1 M M COURSE PART 1 1 M M ORDER ENROLLMENT

  29. MANY-TO-MANY • Many-to-many (M:N)– A relationship between two entities in which an instance (any instance) of entity A can be related to zero, one, or more instances of entity B; ANDan instance of entity B can be related to zero, one, or more instances of entity A Orders (many) Chen Is-ordered-by (many)

  30. MANY-TO-MANY (contents repeated; show Info Engi method) • Many-to-many (M:N)– A relationship between two entities in which an instance (any instance) of entity A can be related to zero, one, or more instances of entity B; ANDan instance of entity B can be related to zero, one, or more instances of entity A TOWN AIRPORT Orders (many) Info Engi Is-ordered-by (many)

  31. RELATIONAL DATA MODEL AND THE DATABASE • Once the ERD is completed, it can be translated from a conceptual logical schema into the formal data model required by the DBMS • Every “box” (entity) in ERD corresponds to one “relation” (table) in the relational data model

  32. FROM ENTITIES TO TABLES • The word “table” is used synonymously with “entity”: an entity is implemented as … • Attributes are implemented as … • Instances are implemented/shown as … • The definition (of table) specifies what will be contained in each column of the table, but does not include data • What other term do we use to refer to a column in a table? • {what are there to describe an entity?} C-11; also C36

  33. FROM ENTITIES TO TABLES • A row in a relation has the following properties: • Only one value at the intersection of a column and row - a relation does not allow multi-valued attributes • Uniqueness - there are no duplicate rows in a relation • Primary key - A field (or group of fields) that uniquely identifies a given entity in a table When would we see this?

  34. FROM ENTITIES TO TABLES • A unique primary key makes it possible to uniquely identify every row in a table • The primary key is important to define and to retrieve every single piece of data in a database • There are only three pieces of information to retrieve for any specific bit of data: • The name of the table • The name of the column • The primary key of the row

  35. FROM ENTITIES TO TABLES • The proper notation to use when documenting the name of the table, the column name, and primary key: • CUSTOMER(Customer Number, First Name, Last Name, Phone Number) • Will follow this notation for DB project • Three qualities of all primary keys: • A primary key should contain some value that is highly unlikely ever to be null • A primary key should never change • Primary key for all rows have distinct values

  36. LOGICALLY RELATING TABLES • The use of iden-tifiers repres-ent relation-ships between entities Primary key; Foreign key

  37. LOGICALLY RELATING TABLES • When a table contains a column that is the same as the primary key of another table, the column is called a foreign key • Foreign key - A primary key of one table that appears as an attribute in another file, and acts to provide a logical relationship between the two files {common column to join two tables} • Example: Prim. Key CUSTOMER(Customer Number, First Name, Last Name, Phone Number) • ORDER(Order Number, Customer Number, Order Date) Refers to For. key

  38. ThreeBasic Operations in a Relational Database • Project:Extracts subset of columnsto create new tables (“views”) • EX: display only last name and GPA • Select:Extracts subset of rows that meet specific criteria • Numeric: salary<40000; • text: city=‘LA’; • date: DOB=#12/12/1972# • Criteria can be combined using AND, OR, etc • Join:Combines relational tablesusing foreign keywhen the data needed is not in one table • Next slide shows examples of the three operations   {Edited & revised by Zhang}

  39. Three Basic Operations in a Relational Database (Laudon) Project Select Join

  40. Creating Database Objects • Four objects: table, query, report, form • Report – a compilation of data from the database that is organized and produced in printed format • Present data in a prescribed format • So data must be obtained from ___? • Changing the report format … changing data? Relationships between the four objects: Forms (Entry, update) Reports (Presentation) Queries (Data Extraction) Tables (Data storage) {Edited & revised by Zhang}

  41. DEALING WITH MANY-TO-MANY RELATIONSHIPS • There are problems with many-to-many relationships • The relational data model cannot handle many-to-many relationships directly • It is limited to one-to-one and one-to-many relationships • Many-to-many relationships need to be replaced with a collection of one-to-many relationships • Relationships cannot have attributes • An entity must represent the relationship •    composite entities

  42. COMPOSITE ENTITIES (optional) • Composite entities - Entities that exist to represent the relationship between two other entities, AKA Intersection entities • Intersection entities are used in the resolution of a many to many relationship • Example: between an ITEM and an ORDER • An ORDER can contain many ITEM(s) and over time, the same ITEM can appear on many ORDER(s)

  43. COMPOSITE ENTITIES / Intersection Entities (optional)

  44. Composite Key – Example (Zhang) Situation: a customer buys certain products on a certain date • Can customer-ID uniquely identify an order line? • Can product-ID uniquely identify an order line? • Can date uniquely identify an order line? • (Answers: …) • Composite key: C-ID, P-ID, Date {Edited & revised by Zhang}

  45. Edited & revised by Zhang Normalization Normalization is a method for analyzing and reducing a relational database to its most streamlined form for: Minimum redundancy Maximum data integrity Best processing performance Normalized data is when attributes in the table depend only on the primary key.

  46. Non-Normalized Relation

  47. Normalizing the Database

  48. Normalizing the Database

  49. Normalization Produces Order

  50. {Edited & revised by Zhang} Example 2: AnUNnormalized Relation of ORDER • If a part appears in many orders, Part_Num, Part_Desc, and Unit_Price will appear in everyone of these orders • If a part appears in many orders, its supplier info – Supp_Num, Supp_Name, Supp_Address - will also appear in every one of these orders • Lots of data redundancy

More Related