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
Bundle: Database Systems: Design, Implementation, & Management, Loose-leaf Version, 12th + Lms Integrated For Mindtap Computing, 1 Term (6 Months) Printed Access Card
- 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_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_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_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_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. 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_forward
- Implement 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_forwardPLZ 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_forward
- Convert 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_forwardWhat would be introduced as criteria of a table being in first normal form? a.) There should be no functional dependencies. b.) The primary key of the table should be composed of one column. c.) Each cell of a table should have a single value. d.) Every functional dependency where X is functionally dependent on Y, X should be the super key of the table.arrow_forwardConsider the Library database given below. Write the following queries in SQL, assuming that the relations are filled with 8 tuples with complete entries and no null values. member (memno, name, age) book (isbn, title, authors, publisher) borrowed (memno, isbn, date) For each publisher, print the members who have borrowed more than two books of that publisher. i. ii. Display name and member number of those members who have not borrowed any book till now. iii. Find out the book titles, which are borrowed by more than one member.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