Concept explainers
Normalization:
The process used to minimize data redundancy and dependency in a relational
First normal form (1NF):
- If a table does not contain any replicate fields or groups of fields then that model is called as first normal form.
- In this form, entities do not contain any single instance of the repeating variable.
- It means that the entities contain only one instance of the attributes, multi-valued attributes are neglected.
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.
Trending nowThis is a popular solution!
Chapter 6 Solutions
Database Systems: Design, Implementation, & Management
- The dependency diagram in Figure Q6.8 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table.FIGURE Q6.8 PRESCRIPTION DEPENDENCY DIAGRAMarrow_forwardER Mapping : Convert the ER diagram into a relational database schema. For each relation, specify the primary keys and the foreign key(s) if any (50 pts) Upload you Relational Diagram here BANK BRANCHES BANK-BRANCH Code Name Addr Addr BranchNo АССTS LOANS N AcctNo Balance LoanNo Amount ACCOUNT Туре LOAN Type M M A-C L-C N Name N. SSN Phone CUSTOMER Addrarrow_forwardCreate a database whose tables are at least in 3NF, showing the dependency diagrams for each table.arrow_forward
- Consider the following database schema for student game, where each team is led by a student of the team and each game is between two teams (Host and Guest): Students Student Id Student Name Address Teams Team Id Team Name Rank Team Leader Membership Student id Team Id Games Game id Score Date Host Team Guest Team Your task is to show all the primary and foreign keys in Schema.arrow_forwardSuppose you are a manufacturer of product ABC, which is composed of parts A, B, and C When a new product is created, the involved parts in table PART must be reduced. As such, when product ABC is created, PROD_QTY is increased by one, and the quantity of parts A, B, and C in table PART (i.e. PART_QTY) is reduced by one respectively. The database content is shown in the following tables. How many database requests can you identify when a product ABC is created? List all.arrow_forwardBelow given relations of a database are in First Normal Form (1* NF). Decompose (Split) the relations to Normalize in 2NF and 3NF. tbl_Project Project Code Project Name Project Manager Budget PC010 Reservation System Mr. Said HR System Attendance System Mr. Rajesh 25000 PC011 MS. Malak 32000 PC012 19000 tbl_Emp_Dept Project Code Emp No Emp Name Dept No Dept Name Hourly Pay PC010 S100 Ali Mohd. D03 Database 7.500 PC010 S101 Hamood D02 Testing 5.500 PC010 S102 Riyaz D01 IT 8.200 PC011 S103 Pavan D03 Database 6.500 PC011 S104 Basma D02 Testing 5.400 PC011 S115 Asaad D01 IT 7.900 PC012 S137 Fathima D03 Database 6.200 PC012 S218 Avinash D02 Testing 6.100 PC012 S109 Роja D01 IT 9.300arrow_forward
- A database table is in 3rd normal form if A It is in 2nd normal form (2NF) and in 1" normal form(INF) B It is in 2NF and all non-key fields depend on all key fields C t is in 2NF and all non-key fields depend only on the key D It is in 2NF and all non-key fields may depend indirectly on all key fields.arrow_forwardname ID admit_date SSN address phone Patient responsible Physician Room id {disjoint,complete) 0..2 Room assigned ISA bed# 1.1 out Patient resident Patient assigned bed (disjoint,complete} Checkback_date discharge_date ISA ICU bed Regular bed Provide a complete Relational Schema of the database described by the following Entity Relationship diagram. You must use Merge Rule whenever possible.arrow_forwardplz help with the following: Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.arrow_forward
- Task 1: ER diagram for give scenario Consider building a database for a shopping mall with requirements as follows: Each product of the shopping mall has a unique project No, and also has a name, a supplier name, a category, a purchase price and a selling price. Products need to be supplied from suppliers. Each product has one supplier, but a supplier may supply many products for the shopping mall. Each supplier has a supplier name and address. A purchase order for product from a supplier has a unique order No, and a date. The order specifies one or more products and their quantities required for a product. A sales order for product to customers has a unique order No, and a date. The order specifies one or more products and their quantities required for a product The shopping mall has warehouses. Each warehouse has a warehouse number, an address and a contact phone number. Each warehouse has shelves to store products. Each shelf has a shelf-number and a capacity. The shelf…arrow_forwardConsider the following database schema for student game, where each team is led by a student of the team and each game is between two teams (Host and Guest): Students Student_Id Student_Name Address Team_Id Team_Name Rank Team_Leader Teams Student_id Team_Id Game_id Score Date Host_Team Guest_Team Membership Games Your task is to show all the primary and foreign keys in Schema.arrow_forwardBelow given relations of a database are in First Normal Form (1st NF). Decompose (Split) the relations to Normalize in 2NF and 3NF. tbl Project Project Code Project Name Project Manager Budget Reservation System HR System Attendance System PC010 Mr. Said 25000 PC011 MS. Malak 32000 PC012 Mr. Rajesh 19000 DBMS tbl Emp Dept Project Code Emp No Hourly Pay Emp Name Dept No Dept Name Ali Mohd. PC010 S100 D03 Database 7.500 PC010 S101 Hamood D02 Testing 5.500 PC010 S102 Riyaz D01 IT 8.200 PC011 S103 Pavan D03 Database 6.500 PC011 S104 Basma D02 Testing 5.400 PC011 S115 Asaad D01 IT 7.900 PC012 S137 Fathima D03 Database 6.200 PC012 S218 Avinash D02 Testing 6.100 PC012 S109 Роoja D01 IT 9.300arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning