Concept explainers
a.
Normalization:
The process used to minimize data redundancy and dependency in a relational
Second normal form (2NF):
- The value of all non-primary key attributes should be dependent on the primary key attribute.
- If any attribute is depending on the partial primary key then it should determine the other attributes for an instance of the entity.
- The partial dependencies should be removed from the data model.
Third normal form (3NF):
- The value of any non-primary key attributes will not depend on any other non-primary key attributes.
- If any non-primary key attributes depend on any other non-primary key attribute then it should be moved or deleted.
- It is termed as transitive dependency.
Partial dependency:
A partial dependency exists at that time of an attributes depends only a part of primary key. This dependency is related with 1st normal form.
Transitive dependency:
A transitive dependency exists at that time of an attributes depends on another attribute which is not part of primary key.
Functional dependency:
An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.
- In a table one attribute is functionally dependent on another attribute to take one value.
a.
Explanation of Solution
Construct the dependency diagram with all partial and transitive dependencies:
The relational schema for given INVOICE table is given below:
Invoice(INV_NUM, PROD_NUM, SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, QUANT_SOLD, PROD_PRICE)
- Here, “INV_NUM” and “PROD_NUM” indicates the primary key.
The representation of dependency diagram with all partial and transitive dependencies is shown below:
Explanation:
In the above dependency diagram,
- The partial dependencies are,
INV_NUM -> (SALE_DATE)
PROD_NUM -> (PROD_LABEL, VEND_CODE, VEND_NAME, PROD_PRICE)
- The transitive dependency is,
VEND_CODE -> (VEND_NAME)
b.
Normalization:
The process used to minimize data redundancy and dependency in a relational database is known as normalization. The database table is divided into two or more tables and defines the relationship between those tables.
Second normal form (2NF):
- The value of all non-primary key attributes should be dependent on the primary key attribute.
- If any attribute is depending on the partial primary key then it should determine the other attributes for an instance of the entity.
- The partial dependencies should be removed from the data model.
Third normal form (3NF):
- The value of any non-primary key attributes will not depend on any other non-primary key attributes.
- If any non-primary key attributes depend on any other non-primary key attribute then it should be moved or deleted.
- It is termed as transitive dependency.
Partial dependency:
A partial dependency exists at that time of an attributes depends only a part of primary key. This dependency is related with 1st normal form.
Transitive dependency:
A transitive dependency exists at that time of an attributes depends on another attribute which is not part of primary key.
Functional dependency:
An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.
- In a table one attribute is functionally dependent on another attribute to take one value.
b.
Explanation of Solution
Construct the dependency diagram by removing all partial dependencies:
The new dependency diagram is represented by removing all partial dependencies in INVOICE table.
First table:
The relational schema for first table is given below:
3NF(INV_NUM, PROD_NUM, SALE_DATE, QUANT_SOLD)
- Here, “INV_NUM” and “PROD_NUM” indicates the primary keys.
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram removes all partial dependencies in first table are shown below:
Second table:
The relational schema for second table is given below:
3NF(INV_NUM, SALE_DATE)
- Here, “INV_NUM” indicates the primary key.
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram removes all partial dependencies in second table are shown below:
Third table:
The relational schema for third table is given below:
2NF(PROD_NUM, PROD_LABEL, VEND_CODE, VEND_NAME, PROD_PRICE)
- Here, “PROD_NUM” indicates the primary key.
- The relation is in third normal form (2NF), since there is transitive dependency in table.
The representation of dependency diagram removes all partial dependencies in third table are shown below:
c.
Normalization:
The process used to minimize data redundancy and dependency in a relational database is known as normalization. The database table is divided into two or more tables and defines the relationship between those tables.
Second normal form (2NF):
- The value of all non-primary key attributes should be dependent on the primary key attribute.
- If any attribute is depending on the partial primary key then it should determine the other attributes for an instance of the entity.
- The partial dependencies should be removed from the data model.
Third normal form (3NF):
- The value of any non-primary key attributes will not depend on any other non-primary key attributes.
- If any non-primary key attributes depend on any other non-primary key attribute then it should be moved or deleted.
- It is termed as transitive dependency.
Partial dependency:
A partial dependency exists at that time of an attributes depends only a part of primary key. This dependency is related with 1st normal form.
Transitive dependency:
A transitive dependency exists at that time of an attributes depends on another attribute which is not part of primary key.
Functional dependency:
An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.
- In a table one attribute is functionally dependent on another attribute to take one value.
c.
Explanation of Solution
Construct the dependency diagram by removing all transitive dependencies:
The new dependency diagram is represented by removing all transitive dependencies in INVOICE table.
First table:
The relational schema for first table is given below:
3NF(INV_NUM, PROD_NUM, QUANT_SOLD)
- Here, “INV_NUM” and “PROD_NUM” indicates the primary keys.
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram removes all partial dependencies in first table are shown below:
Second table:
The relational schema for second table is given below:
3NF(INV_NUM, SALE_DATE)
- Here, “INV_NUM” indicates the primary key.
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram removes all transitive dependencies in second table are shown below:
Third table:
The relational schema for third table is given below:
3NF(VEND_CODE, VEND_NAME)
- Here, “VEND_CODE” indicates the primary key.
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram removes all transitive dependencies in third table are shown below:
Fourth table:
The relational schema for final table is given below:
2NF(PROD_NUM, PROD_LABEL, VEND_CODE, PROD_PRICE)
- Here, “PROD_NUM” indicates the primary key.
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram removes all transitive dependencies are shown below:
d.
Explanation of Solution
The representation of Crow’s Foot Entity Relational Diagram (ERD) is shown below:
The following data model shows the solution for the given question.
Explanation:
- In the above data model, one invoice can contains many invoice line.
- The “INVOICE” entity contains the “INV_NUM” and “SALE_DATE” attributes.
- The primary key of “INVOICE” entity is “INV_NUM”.
- The “INV_LINE” entity contains the “INV_NUM”, “PROD_NUM” and “QUANT_SOLD” attributes.
- The primary and foreign key of this entity is “INV_NUM”.
- The foreign key of this entity is “PROD_NUM”.
- The “INVOICE” entity contains the “INV_NUM” and “SALE_DATE” attributes.
- The product is written in more than one invoice line.
- The “PRODUCT” entity contains the “PROD_NUM”, “VEND_CODE”, “PROD_LABEL”, and “PROD_PRICE” attributes.
- The primary key of “PROD_NUM” for this entity.
- The foreign key of this entity is “VEND_CODE”.
- The “PRODUCT” entity contains the “PROD_NUM”, “VEND_CODE”, “PROD_LABEL”, and “PROD_PRICE” attributes.
- The vendor supplies more than one product.
- The “VENDOR” entity contains the “VEND_CODE” and “VEND_NAME” attributes.
- The primary key of “VEND_CODE” entity.
- The “VENDOR” entity contains the “VEND_CODE” and “VEND_NAME” attributes.
Want to see more full solutions like this?
Chapter 6 Solutions
Database Systems: Design, Implementation, & Management
- Using the STUDENT table structure in picture, do the following: 1. Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.) 2. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. 3. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created. 4. Draw the Crow’s Foot ERD.arrow_forwardUsing the INVOICE table structure shown in Table P6.3, do the following:TABLE P6.3a. Write the relational schema, draw its dependency diagram, and identify alldependencies, including all partial and transitive dependencies. You can assumethat the table does not contain repeating groups and that an invoice numberreferences more than one product. (Hint: This table uses a composite primarykey.)b. Remove all partial dependencies, write the relational schema, and draw thenew dependency diagrams. Identify the normal forms for each table structureyou created.NoteYou can assume that any given product is supplied by a single vendor, but a vendorcan supply many products. Therefore, it is proper to conclude that the followingdependency exists:PROD_NUM → PROD_LABEL, PROD_PRICE, VEND_CODE, VEND_NAME(Hint: Your actions should produce three dependency diagrams.)c. Remove all transitive dependencies, write the relational schema, and draw thenew dependency diagrams. Also identify the normal forms for…arrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…arrow_forward
- Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…arrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…arrow_forwardGiven the sample records in the CHARTER table shown in Table P6.11, do the following:a. Write the relational schema and draw the dependency diagram for the table structure. Make sure that you label all dependencies. CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is based on round-trip miles, including pickup points. (Hint: Look at the data values to determine the nature of the relationships. For example, note that employee Melton has flown two charter trips as pilot and one trip as copilot.)b. Decompose the dependency diagram you drew to solve Problem 11a to create table structures that are in 3NF and write the relational schema.c. Draw the Crow's Foot ERD to reflect the properly decomposed dependency diagrams you created in Problem 11b. Make sure the ERD yields a database that can track all of the data shown in Problem 11. Show all entities, relationships, connectivities, optionalities, and cardinalities.To upload and submit your assignment, click the Choose…arrow_forward
- Given the sample records in the CHARTER table shown in Table P6.11, do the following: A. Write the relational schema and draw the dependency diagram for the table structure. Make sure that you label all dependencies. CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is based on round-trip miles, including pickup points. (Hint: Look at the data values to determine the nature of the relationships. For example, note that employee Melton has flown two charter trips as pilot and one trip as copilot. B. Provide the 3rd Normal Form Tables based on the information in Table P6.11 C. Once you have converted to 3rd Normal form, draw the normalized ER Modelarrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the diagram below The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply these…arrow_forwardGiven the sample records in the CHARTER table shown in Table P6.11, do the following:a. Write the relational schema and draw the dependency diagram for the table structure. Make sure that youlabel all dependencies. CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry isbased on round-trip miles, including pickup points. (Hint: Look at the data values to determine the natureof the relationships. For example, note that employee Melton has flown two charter trips as pilot and onetrip as copilot.)b. Decompose the dependency diagram you drew to solve Problem 11a to create table structures that are in3NF and write the relational schema.c. Draw the Crow’s Foot ERD to reflect the properly decomposed dependency diagrams you created inProblem 11b. Make sure the ERD yields a database that can track all of the data shown in Problem 11.Show all entities, relationships, connectivities, optionalities, and cardinalities.arrow_forward
- PLZ help with the following: Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriate data types for each attribute and include primary key and foreign key constraints, Check and Not Null constraints. Normalize the relations to 3NF.arrow_forwardConvert this table to 1st normal form. Identify the primary key(s) in the table. Draw as-is dependency diagram and identify types of dependencies. Is this table 3rd normal form? If not, convert it to 3rd normal form. Student Student First Student Birth Course Course Course ID Name Date ID Grade Prerequisite ID 16830 Williams 1/31/2000 IS 350 A IS 300 16830 Williams 1/31/2000 IS 465 B IS300, IS 350 54329 Baker 2/29/1998 IS 350 A IS 300 54329 Baker 2/29/1998 IS 465 A IS300, IS 350arrow_forward[slide-19.png] Q1. Please write SQL codes to create below reservation database table based on the previous slide (slide 19) Video E-R Modling Diagram child table RESERVATION 1. Reservation table is a child table, and the member_id column is a foreign key column which references member table’s member_id primary key column. 2. Reservation table is a child table, and the reserve_date and member_id columns are combined together as a composite primary key column. DESC reservation Name Null? Type reserve_date NOT NULL DATE member_ID NOT NULL NUMBER(4, 0) title_ID NOT NULL NUMBER(3, 0)arrow_forward
Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education