ilovepdf_merged (1).pdf

Chapter 3 The Enhanced E-R Model Instructor: Dicle Yagmur Ozdemir Lecture 3 Sept 17th, 2024 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Learning Objectives 3.1 Define terms 3.2 Understand use of supertype/subtype relationships 3.3...

Chapter 3 The Enhanced E-R Model Instructor: Dicle Yagmur Ozdemir Lecture 3 Sept 17th, 2024 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Learning Objectives 3.1 Define terms 3.2 Understand use of supertype/subtype relationships 3.3 Use specialization and generalization techniques 3.4 Specify completeness and disjointness constraints 3.5 Develop supertype/subtype hierarchies for business situations 3.6 Develop entity clusters 3.7 Explain universal (packaged) data model Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Supertypes and Subtypes Enhanced E-R (EER) model: extends original E-R model with new modeling constructs Subtype: A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings Supertype: A generic entity type that has a relationship with one or more subtypes Attribute Inheritance: – Subtype entities inherit values of all attributes and relationships of the supertype – An instance of a subtype is also an instance of the supertype Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-1 Basic Notation for Supertype/Subtype Relationships (1 of 2) a) EER notation Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-2 Employee Supertype with Three Subtypes All employee subtypes will have employee number, name, address, and date hired Each employee subtype will also have its own attributes Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Relationships and Subtypes Relationships at the supertype level indicate that all subtypes will participate in the relationship The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-3 Supertype/Subtype Relationships in a Hospital Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Generalization and Specialization Generalization: The process of defining a more general entity type from a set of more specialized entity types. Bottom-Up Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. Top-Down Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-4 Example of Generalization (1 of 2) a) Three entity types: Car, Truck, and Motorcycle All these types of vehicles have common attributes Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-4 Example of Generalization (2 of 2) b) Generalization to Vehicle supertype We put the shared attributes in a supertype. Note: no subtype for motorcycle, since it has no unique attributes Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-5 Example of Specialization (1 of 2) a) Entity type Part Note: Routing Number only applies if part is manufactured in house. Supplier only applies if part is purchased from a supplier. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-5 Example of Specialization (2 of 2) b) Specialization to Manufactured Part and Purchased Part Multivalued composite attribute replaced by associative entity relationship to another entity Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Constraints in Supertype/Subtype Relationships Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype – Total Specialization Rule: Yes (double line) – Partial Specialization Rule: No (single line) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-6 Examples of Completeness Constraints (1 of 2) a) Total specialization rule Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-6 Examples of Completeness Constraints (2 of 2) b) Partial specialization rule Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Constraints in Supertype/Subtype Relationships (1 of 2) Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes – Disjoint Rule: An instance of the supertype can be only One of the subtypes – Overlap Rule: An instance of the supertype could be more than one of the subtypes Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-7 Examples of Disjointness Constraints (1 of 2) a) Disjoint rule Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-7 Examples of Disjointness Constraints (2 of 2) b) Overlap rule Overlap rule: A Part may be both a Manufactured Part and a Purchased Part at the same time. An instance of PART is a particular type of part, not an individual part. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Constraints in Supertype/Subtype Relationships (2 of 2) Subtype Discriminator: An attribute of the supertype whose values determine the target subtype(s) – Disjoint – a simple attribute with alternative values to indicate the possible subtypes – Overlapping – a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-8 Introducing a Subtype Discriminator (Disjoint Rule) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-9 Subtype Discriminator (Overlap Rule) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-10 Example of Supertype/Subtype Hierarchy Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-10 Example of Supertype/Subtype Hierarchy Question: Note here that a person must be either an employee, alumnus, or student. Is it possible for a person to be both an employee and a student? Why or why not? Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-10 Example of Supertype/Subtype Hierarchy Question: Can you envision what the Person’s subtype discriminator would be? Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-10 Example of Supertype/Subtype Hierarchy Question: Is it possible for an employee to be something other than Faculty or Staff? Why or why not? Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-10 Example of Supertype/Subtype Hierarchy Question: Is it possible for an employee to be both faculty and staff? Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-10 Example of Supertype/Subtype Hierarchy Question: Is it possible for a staff member to also be a graduate student? Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-10 Example of Supertype/Subtype Hierarchy Question: Is it possible for someone to have more than one degree from this university? Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Entity Clusters EER diagrams are difficult to read when there are too many entities and relationships. Solution: Group entities and relationships into entity clusters. Entity cluster: Set of one or more entity types and associated relationships grouped into a single abstract entity type Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-13 Entity Clustering for Pine Valley Furniture Company (1 of 2) a) Possible entity clusters (using Microsoft Visio) Related groups of entities could become clusters Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 3-13 Entity Clustering for Pine Valley Furniture Company (2 of 2) b) EER diagram for entity clusters (using Microsoft Visio) More readable, isn’t it? Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Packaged Data Models Predefined data models Could be universal or industry-specific Universal data model = a generic or template data model that can be reused as a starting point for a data modeling project (also called a “pattern”) Requires customization Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Advantages of Packaged Data Models Use proven model components Save time and cost Less likelihood of data model errors Easier to evolve and modify over time Aid in requirements determination Supertype/subtype hierarchies Universal models support interorganizational systems Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Review Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Which of the following is a generic entity type that has a relationship with one or more subtypes? A) Megatype B) Supertype C) Subgroup D) Class Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  In the figure below, which of the following apply to both OUTPATIENTs and RESIDENT_PATIENTs? A) Checkback_Date B) Date_Discharged C) Patient_Name D) XML Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  In the figure below, which of the following is a subtype of patient? A) Outpatient B) Physician C) Bed D) Date_Hired Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  The process of defining one or more subtypes of a supertype and forming relationships is called: A) specialization. B) generalization. C) creating discord. D) selecting classes. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  The ________ rule states that an entity instance can simultaneously be a member of two (or more) subtypes. A) disjoint B) overlap C) partial specialization D) total specialization Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  The subtype discriminator in the figure below is: A) Part_Type. B) Part_No. C) Manufactured Part. D) Location. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  The following figure shows an example of: A) the disjoint rule. B) the completeness rule. C) the underdog rule. D) the overlap rule. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  In a supertype/subtype hierarchy, each subtype has: A) only one supertype. B) many supertypes. C) at most two supertypes. D) at least one subtype. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  An entity cluster can be formed by: A) deleting a supertype and its subtype. B) combining metadata. C) combining a strong entity and its weak entities. D) deleting metadata. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Packaged data models: A) are ready to use right out of the box. B) require customization. C) allow partial specialization. D) cannot be used for most applications. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Chapter 5 Introduction to SQL Instructor: Dicle Yagmur Ozdemir Lecture 5 Oct 1st, 2024 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Learning Objectives 5.1 Define terms 5.2 Define a database using the SQL data definition language 5.3 Write single-table queries using SQL commands 5.4 Establish referential integrity using SQL Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved SQL Overview Structured Query Language – often pronounced “Sequel” The standard for Relational Database Management Systems (RDBMS) RDBMS: A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved SQL Environment Data Definition Language (DDL) – Commands that define a database, including creating, altering, and dropping tables and establishing constraints Data Manipulation Language (DML) – Commands that maintain and query a database (updating, inserting, modifying, querying) Data Control Language (DCL) – Commands that control a database, including administering privileges and committing data Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 5-1 A Simplified Schematic of a Typical SQL Environment, as Described by the SQL:2016 Standards Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved SQL Data Types Strings – CHARACTER (n), VARYING CHARACTER (n) Binary – Binary Large Object (BLOB) Number – Numeric (precision, scale), Decimal (p, s), Integer Temporal – Timestamp, Timestamp with local time zone Boolean – True or False values Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 5-4 DDL, DML, DCL, and the Database Development Process Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved SQL Database Definition Data Definition Language (DDL) Major CREATE statements: – CREATE SCHEMA – defines a portion of the database owned by a particular user – CREATE TABLE – defines a new table and its columns – CREATE VIEW – defines a logical table from one or more tables or views Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Steps in Table Creation 1. Identify data types for attributes 2. Identify columns that can and cannot be null 3. Identify columns that must be unique (candidate keys) 4. Identify primary key–foreign key mates 5. Determine default values 6. Identify constraints on columns (domain specifications) 7. Create the table Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved The Following Slides Create Tables for This Enterprise Data Model (from Chapter 1, Figure 1-3) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 5-6 SQL Database Definition Commands for Pine Valley Furniture Company (Oracle 12c) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Defining Attributes and Their Data Types Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Non-Nullable Specifications Some primary keys are composite– composed of multiple attributes Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Controlling the Values in Attributes Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Identifying Foreign Keys and Establishing Relationships Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Data Integrity Controls Referential integrity – constraint that ensures that foreign key values of a table must match primary key values of a related table in 1: N relationships Restricting: – Deletes of primary records – Updates of primary records – Inserts of dependent records Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 5-7 Ensuring Data Integrity Through Updates Relational integrity is enforced via the primary-key to foreign-key match Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Changing Tables ALTER TABLE statement allows you to change column specifications: – ALTER TABLE table_name alter_table_action; Table Actions: – ADD [COLUMN] column_definition – ALTER [COLUMN] column_name SET DEFAULT default- value Example (adding a new column with a default value): – ALTER TABLE CUSTOMER_T ADD CustomerType VARCHAR2 (10) DEFAULT ‘Commercial’; Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Removing Tables DROP TABLE statement allows you to remove tables from your schema: – DROP TABLE Customer_T Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved INSERT Statement Adds one or more rows to a table Inserting into a table: – INSERT INTO Customer_T VALUES (001, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, ‘32601’); Inserting a record that has some null attributes requires identifying the fields that actually get data: – INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8); Inserting from another table: – INSERT INTO CaCustomer_T SELECT * FROM Customer_T WHERE CustomerState = ‘CA’; Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved DELETE Statement Removes rows from a table Delete certain rows – DELETE FROM Customer_T WHERE CustomerState = ‘HI’; Delete all rows – DELETE FROM Customer_T; Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved UPDATE Statement Modifies data in existing rows – UPDATE Product_T SET ProductStandardPrice = 775 WHERE ProductID = 7; Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved SELECT Statement Used for queries on single or multiple tables Clauses of the SELECT statement: – SELECT: List the columns (and expressions) to be returned from the query – FROM: Indicate the table(s) or view(s) from which data will be obtained – WHERE: Indicate the conditions under which a row will be included in the result – GROUP BY: Indicate categorization of results – HAVING: Indicate the conditions under which a category (group) will be included – ORDER BY: Sorts the result according to specified criteria Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved SELECT Example Find products with standard price less than $275 Comparison operators include – = Equal to – > Greater than – >= Greater than or equal to – < Less than – 300; Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 5-8 Boolean Query a Without the Use of Parentheses By default, processing order of Boolean operators is NOT, then AND, then OR Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved SELECT Example–Boolean Operators With parentheses…these override the normal precedence of Boolean operators SELECT ProductDescription, ProductFinish, ProductStandardPrice FROM Product_T WHERE (ProductDescription LIKE ‘%Desk’ OR ProductDescription LIKE ‘%Table’) AND ProductStandardPrice > 300; With parentheses, you can override normal precedence rules. In this case parentheses make the OR take place before the AND. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 5-9 Boolean Query B With Use of Parentheses With parentheses, you can override normal precedence rules. In this case parentheses make the OR take place before the AND. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Sorting Results With ORDER BY Clause Sort the results first by STATE, and within a state by the CUSTOMER NAME SELECT CustomerName, CustomerCity, CustomerState FROM Customer_T WHERE CustomerState IN (‘FL’, ‘TX’, ‘CA’, ‘HI’) ORDER BY CustomerState, CustomerName; Note: The IN operator in this example allows you to include rows whose CustomerState value is either FL, TX, CA, or HI. It is more efficient than separate OR conditions. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Categorizing Results Using GROUP BY Clause For use with aggregate functions – Scalar aggregate: single value returned from SQL query with aggregate function – Vector aggregate: multiple values returned from SQL query with aggregate function (via GROUP BY) SELECT CustomerState, COUNT (CustomerState) FROM Customer_T GROUP BY CustomerState Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Qualifying Results by Categories Using the HAVING Clause For use with GROUP BY SELECT CustomerState, COUNT (CustomerState) FROM Customer_T GROUP BY CustomerState HAVING COUNT (CustomerState) > 1; Like a WHERE clause, but it operates on groups (categories), not on individual rows. Here, only those groups with total numbers greater than 1 will be included in the final result. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved A Query With Both WHERE and HAVING SELECT ProductFinish, AVG (ProductStandardPrice) FROM Product_T WHERE ProductFinish IN (‘Cherry’, ‘Natural Ash’, ‘Natural Maple’, ‘White Ash’) GROUP BY ProductFinish HAVING AVG (ProductStandardPrice) < 750 ORDER BY ProductFinish; Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 5-10 SQL Statement Processing Order SELECT [ALL/DISTINCT] column_list FROM table list [WHERE conditional expression] [GROUP BY group_by_column_list] [HAVING conditional expression] [ORDER BY order_by_column_list] (based on van der Lans, 2006, p. 100) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise - 1 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise - 2 Table called world that contains four columns: – country name, – 3-letter country abbreviation, – country GDP, and – country population. Names of these columns are country, abbrv, gdp, and population. This table consists of data collected in 2014 from 148 countries. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise - 2 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise - 2 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise – 3 Northwind Traders The sales data for Northwind Traders, a fictitious specialty foods export- import company Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise - 3 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise - 3 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Review Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  ________ is a set of commands used to update and query a database. A) DML B) DDL C) DCL D) DPL Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  The command for removing a table is: A) CREATE TABLE. B) REMOVE TABLE. C) DROP TABLE. D) TRUNCATE TABLE. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  The SQL command ________ adds one or more new columns to a table. A) CREATE TABLE B) ALTER TABLE C) CREATE VIEW D) CREATE RELATIONSHIP Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  What does the following SQL statement do? UPDATE Product_T SET Unit_Price = 775 WHERE Product_ID = 7 A) Changes the price of a unit called Product_T to 7 B) Changes the unit price of Product 7 to 775 C) Changes the length of the Unit_Price field to 775 D) Updates the Product_T table to have a unit price of 775 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  The following two SQL statements will produce the same results. SELECT last_name, first_name FROM customer WHERE credit_limit > 99 AND credit_limit < 10001; SELECT last_name, first_name FROM customer WHERE credit_limit BETWEEN 100 and 10000; Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Chapter 4 Logical Database Design and the Relational Model Instructor: Dicle Yagmur Ozdemir Lecture 4 Sept 24th, 2024 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Learning Objectives 4.1 Define terms 4.2 List five properties of relations 4.3 State two properties of candidate keys 4.4 Define first, second, and third normal form 4.5 Describe problems from merging relations 4.6 Transform E-R and EER diagrams to relations 4.7 Create tables with entity and relational integrity constraints 4.8 Use normalization to decompose anomalous relations to well-structured relations Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Components of Relational Model Data structure – Tables (relations), rows, columns Data manipulation – Powerful SQL operations for retrieving and modifying data Data integrity – Mechanisms for implementing business rules that maintain integrity of manipulated data Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Relation A relation is a named, two-dimensional table of data. Consists of rows (records) and columns (attribute or field) Requirements for a table to qualify as a relation: – It must have a unique name. – Every attribute value must be atomic (not multivalued, not composite). – Every row must be unique (can’t have two rows with exactly the same values for all their fields). – Attributes (columns) in tables must have unique names. – The order of the columns must be irrelevant. – The order of the rows must be irrelevant. Note: All relations are in 1st Normal form. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Correspondence with E-R Model Relations (tables) correspond with entity types Rows correspond with entity instances Columns correspond with attributes. Note: The word relation (in relational database) is not the same as the word relationship (in E-R model). Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Key Fields Keys are special fields that serve two main purposes: – Primary key is an attribute (or a combination of attributes) that uniquely identifies each row in a relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique. – Foreign keys are attributes in a relation that serves as the primary key of another relation (thereby representing the association between two relations (or tables)). – Keys can be simple (a single field) or composite (more than one field). Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-3 Schema for Four Relations (Pine Valley Furniture Company (a) EER notation Primary Key Foreign Key (implements 1:N relationship between customer and order) Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M:N relationship between order and product) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Schema for four relations (Pine Valley Furniture Company) TEXTUAL REPRESENTATION CUSTOMER(CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) ORDER(OrderID, OrderDate, CustomerID) ORDER LINE(OrderID, ProductID, OrderedQuantity) PRODUCT(ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID) NOTE: If an attribute serves both as a foreign key and as part of a primary key, it is just underlined Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Integrity Constraints (1 of 2) Domain Constraints – Allowable values for an attribute Entity Integrity – No primary key attribute may be null. All primary key fields must contain data values. Referential Integrity – Enforcing associations between relations (tables) through the use of foreign keys Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Integrity Constraints (2 of 2) A rule stating that either each foreign key value must match a primary key value in another relation or the foreign key value must be null Delete Rules (i.e., what happens when you want to delete a customer who has submitted orders) Restrict–prohibit deletion of the customer until all associated orders are first deleted Cascade–delete the associated orders of the customer Set-to-Null–place a null value in the foreign key of order table Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-5 Referential Integrity Constraints (Pine Valley Furniture Company) Referential integrity constraints are drawn via arrows from dependent to parent table Referential integrity constraints are shown using arrows drawn from foreign keys to primary keys Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-6 SQL table definitions Integrity constraints are implemented using SQL DDL statements Domain constraints (data type and data length specifications) Entity integrity constraints (primary key clause and not null constraint for primary key attributes) Referential integrity constraints (foreign key to primary key references) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Transforming EER Diagrams into Relations (1 of 7) Mapping Regular Entities to Relations – Simple attributes: E-R attributes map directly onto the relation – Composite attributes: Use only their simple, component attributes – Multivalued attributes: Become a separate relation with a foreign key taken from the superior entity Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-8 Example of Mapping a Regular Entity (a) CUSTOMER entity type The identifier of the entity type (b) CUSTOMER relation becomes the primary key of the corresponding relation Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-9 Example of Mapping a Composite Attribute (a) CUSTOMER entity type with composite attribute (b) CUSTOMER relation with address detail Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-10 Example of Mapping an Entity with a Multivalued Attribute (a) EMPLOYEE entity type with multivalued attribute Multivalued (b) EMPLOYEE and EMPLOYEE SKILL relations attribute becomes a separate relation with a foreign key EmployeeID and Skill serve as the primary key of the new relation Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Transforming EER Diagrams into Relations (2 of 7) Mapping Weak Entities – Becomes a separate relation with a foreign key taken from the superior entity – Primary key composed of: ▪ Partial identifier of weak entity ▪ Primary key of identifying relation (strong entity) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved For a given employee, Dependent Name uniquely identifies a dependent Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  NOTE: the domain constraint for the foreign key should NOT allow null value if DEPENDENT is a weak entity Foreign key Composite primary key When a composite primary key is long (i.e., more than four components), a surrogate primary key can be assigned Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Transforming EER Diagrams into Relations (3 of 7) Mapping Binary Relationships – One-to-Many – Primary key on the one side becomes a foreign key on the many side – Many-to-Many – Create a new relation with the primary keys of the two entities as its primary key – One-to-One – Primary key on mandatory side becomes a foreign key on optional side Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-12 Example of Mapping a 1: M 1 colon N Relationship (a) Relationship between CUSTOMER and ORDER entities Note the mandatory one (b) CUSTOMER and ORDER relations with a foreign key in ORDER Again, no null value in the foreign key…this is because of the mandatory minimum cardinality Foreign key Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-13 Example of Mapping a M :N M colon N Relationship (a) Completes relationship (M : N ) (b) Three resulting relations Composite primary key Attribute of the relationship is Foreign Foreign key key included in the new relation Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-14 Example of Mapping a Binary 1:1 Relationship 1 colon 1 (a) In Charge relationship (binary 1: 1) Often in 1:1 relationships, one direction is optional (b) Resulting relations Foreign key (NurseInCharge) goes in the relation on the optional side, matching the primary key (NurseID) on the mandatory side DateAssigned goes in the relation on the optional side, and would NOT be null Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Transforming EER Diagrams into Relations (4 of 7) Mapping Associative Entities – Identifier Not Assigned ▪ Default primary key for the association relation is composed of the primary keys of the two entities (as in M : N relationship) – Identifier Assigned ▪ It is natural and familiar to end-users ▪ Default identifier may not be unique Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-15 Example of Mapping an Associative Entity (1 of 2) (a) An associative entity (identifier not assigned) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-15 Example of Mapping an Associative Entity (2 of 2) (b) Three resulting relations Composite primary key formed from the two foreign keys Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-16 Example of Mapping an Associative Entity with an Identifier (1 of 2) (a) SHIPMENT associative entity Customer ID and Vendor ID together may not uniquely identify the instances of SHIPMENT Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-16 Example of Mapping an Associative Entity with an Identifier (2 of 2) (b) Three resulting relations Primary key differs from foreign keys Primary key differs from foreign keys Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Transforming EER Diagrams into Relations (5 of 7) Mapping Unary Relationships – One-to-Many – Recursive foreign key in the same relation – Many-to-Many – Two relations: ▪ One for the entity type ▪ One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-17 Example of Mapping a Unary 1: M Relationship 1 colon N (a) EMPLOYEE entity with unary relationship (b) EMPLOYEE relation with recursive foreign key Recursive foreign key MUST have the same domain as the primary key Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-18 Example of Mapping a Unary M : N Relationship M colon N (a) Bill-of-materials relationship Contains (M : N ) (b) ITEM and COMPONENT relations Both foreign keys reference the same primary key (All three have the same domain) Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Transforming EER Diagrams into Relations (6 of 7) Mapping Ternary (and n-ary) Relationships – One relation for each entity and one for the associative entity – Associative entity has foreign keys to each entity in the relationship Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-19 Example of Mapping a Ternary Relationship (1 of 2) (a) PATIENT TREATMENT ternary relationship with associative entity Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-19 Example of Mapping a Ternary Relationship (2 of 2) (b) Four resulting relations Remember This is why But this makes a It would be that the treatment date very better to create a primary key and time are cumbersome surrogate key MUST be included in the key… like Treatment# unique composite primary key Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Transforming EER Diagrams into Relations (7 of 7) Mapping Supertype/Subtype Relationships – One relation for supertype and for each subtype – Supertype attributes (including identifier and subtype discriminator) go into supertype relation – Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-20 Supertype/Subtype Relationships Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-21 Example of Mapping Supertype/Subtype Relationships to Relations A prefix is used to distinguish the name of the primary key for each subtype relation. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise – 1 Develop a relational schema Figure shows an EER diagram for a simplified credit card environment. There are two types of card accounts: debit cards and credit cards. Credit card accounts accumulate charges with merchants. Each charge is identified by the date and time of the charge as well as the primary keys of merchant and credit card. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Review Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved A form of database design which maps conceptual requirements is called: A) logical designs. B) response designs. C) security design. D) physical design. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Data is represented in the form of _________ in a relational model. A) data trees B) tables C) rows D) columns Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  A two-dimensional table of data sometimes is called a: A) group. B) set. C) declaration. D) relation. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  An attribute (or attributes) that uniquely identifies each row in a relation is called a: A) column. B) foreign field. C) primary key. D) duplicate key. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a: A) link attribute. B) link key. C) foreign key. D) foreign attribute. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Data Normalization Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data The process of decomposing relations with anomalies to produce smaller, well-structured relations Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Well-Structured Relations Relations that contain minimal data redundancy and allow users to insert, delete, and update rows without causing data inconsistencies Goal is to avoid anomalies – Insertion Anomaly – adding new rows forces user to create duplicate data – Deletion Anomaly – deleting rows may cause a loss of data that would be needed for other future rows – Modification Anomaly – changing data in a row forces changes to other rows because of duplication Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Example–Figure 4-2b EMPLOYEE2 EmpID Name DeptName Salary CourseTitle DateCompleted 100 Margaret Simpson Marketing 48,000 SPSS 6/19/2018 100 Margaret Simpson Marketing 48,000 Surveys 10/7/2018 140 Alan Beeton Accounting 52,000 Tax Acc 12/8/2018 110 Chris Lucero Info Systems 43,000 Visual Basic 1/12/2018 110 Chris Lucero Info Systems 43,000 C++ 4/22/2018 Blank Blank 190 Lorenzo Davis Finance 55,000 150 Susan Martin Marketing 42,000 SPSS 6/19/2018 150 Susan Martin Marketing 42,000 Java 8/12/2018 Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Anomalies in This Relation (1 of 2) Insertion – can’t enter a new employee without having the employee take a class (or at least empty fields of class information) Deletion – if we remove employee 140, we lose information about the existence of a Tax Acc class Modification – giving a salary increase to employee 100 forces us to update multiple records Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4.22 Steps in Normalization Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Functional Dependencies and Keys Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute Candidate Key: – A unique identifier. One of the candidate keys will become the primary key ▪ E.g., perhaps there is both credit card number and SS# in a table…in this case both are candidate keys. – Each non-key field is functionally dependent on every candidate key. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved First Normal Form No multivalued and composite attributes Every attribute value is atomic All relations are in 1st Normal Form. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4.25 Invoice Data (Pine Valley Furniture Company) This is not a relation. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4.26 Invoice Relation (1NF) (Pine Valley Furniture Company) This is a relation, but not a well-structured one. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Anomalies in This Relation (2 of 2) Insertion – if new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication Deletion – if we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price Update – changing the price of product ID 4 requires update in multiple records Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Second Normal Form 1NF plus every non-key attribute is fully functionally dependent on the ENTIRE primary key – Every non-key attribute must be defined by the entire key, not by only part of the key – No partial functional dependencies Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-27 Functional Dependency Diagram for Invoice OrderID → OrderDate, CustomerID, CustomerName, CustomerAddress CustomerID → CustomerName, CustomerAddress ProductID → ProductDescription, ProductFinish, ProductStandardPrice OrderID, ProductID → OrderQuantity Therefore, not in 2nd Normal Form Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-28 Removing Partial Dependencies Getting it into Second Normal Form Partial dependencies are removed, but there are still transitive dependencies Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Third Normal Form 2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes) Note: This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Figure 4-29 Removing Transitive Dependencies Getting it into Third Normal Form Transitive dependencies are removed. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Merging Relations View Integration – Combining entities from multiple E-R models into common relations Issues to watch out for when merging entities from different E-R models: – Synonyms – two or more attributes with different names but same meaning – Homonyms – attributes with same name but different meanings – Transitive dependencies – Supertype/subtype relationships Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved Classroom Exercise - 2 Table 4.5 shows a shipping manifest. a. Draw a relation and show the functional dependencies in the relation b. In what normal form is this relation? c. Decompose MANIFEST into a set of 3NF relations Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Review Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  In the figure below, the primary key for "Order Line" is which type of key? A) Composite B) Foreign C) Multivalued D) Grouped Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  Which of the following are properties of relations? A) Each attribute has the same name. B) No two rows in a relation are identical. C) There must be multivalued attributes in a relation. D) All columns are numeric. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  All of the following are the main goals of normalization EXCEPT: A) minimize data redundancy. B) simplify the insertion, deletion, and modification of data. C) maximize storage space. D) make it easier to maintain data. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  When all multivalued and composite attributes have been removed from a relation, it is said to be in: A) first normal form. B) second normal form. C) Boyce-Codd normal form. D) third normal form. Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved  ________ anomalies can be caused by editing data in tables. A) Insertion B) Deletion C) Modification D) Creation Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved