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

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