Concept explainers
a.
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.
b.
Explanation of Solution
Dependencies diagrams for each database table:
Table1:
Create the database table with name of Table1 is given below:
Table1 (C1, C2)
- C2 are partial dependent on C1.
Normal form:
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram for table1 is shown below:
c.
Explanation of Solution
Dependencies diagrams for each database table:
Table1:
Create the database table with name of Table1 is given below:
Table1 (C1, C2)
- Here, C1 indicate the primary key.
- C2 are partial dependent on C1.
Normal form:
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
The representation of dependency diagram for table1 is shown below:
Table2:
Create the database table with name of Table2 is given below:
Table2 (C1, C3, C4)
- Here, C1 and C3 indicate the primary key and C1 indicates the foreign key that belongs to Table1 and C4 indicates the foreign key that belongs to Table3.
Normal form:
- The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes...
Trending nowThis is a popular solution!
- Question 2:- The dependency diagram shown indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book. ISBN Book Title Author Num LastName Publisher Royalty Edition a) Based on the dependency diagram, create a database whose tables are at least in 2NF, showing the dependency diagram for each table. For full points, make sure to clearly label all partial and transitive dependencies.arrow_forward1. what is the relationship between Functional Dependency and Normalization? 2. partial dependency and transitive, give examples of the two relationships. 3. Why do we do the normalization stage in database design, especially in the logical stage?arrow_forwardAll of you must be familier with human resources system (employee database). Soplease design database for a human resources system that at least has 4 entitiesinvolved and for the 4 entities please do the following:a. Create the tables that conform to 3NF.b. Draw the Crow’s Foot ERD to reflect the dependency and relationshipdiagrams;c. For all entities/tables created please write the MASTER and TRANSACTIONdata accordingly!d. Use SQL commands to to do selection which involve 1 table, 2 tables, and 3tables.arrow_forward
- Consider the following relational schema for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price, bookCoverType, contractDate) Collection (Title, Author, Catalog_no) Assume {Author, Title} is the key for both relations Additional functional dependencies are Title,Author --> Catalog_no Catalog_no --> Publisher, Year, bookCoverType Publisher, bookCoverType --> Price Author --> contractDate a) Explain what normal form the relation is in. b) Apply normalization until the 3rd State reasons behind each normalizationarrow_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_forwardSubject: Database Topic: EERD Please use UML Modelling Notation. (Don't use Chen’s Notation or Crow’s Feet Notation). The attached EERD is not the answer. it's for your reference only about UML Modelling Notation. -Based on the information given below, draw the conceptual EERD where you should include the enhanced features. You are required to design a database for a car championship. The requirements are as follows: In the championship, each car’s information such as the car model, the top speed and the acceleration will be stored, and it is identifiable through a unique car ID. Each car will be drove by only one driver. Each driver’s info such as the unique driver number, driver name and the experience level of the driver are stored as well. A driver may have many trainers from more experience drivers and these experience drivers would have at least one trainee who follow them. Each of these drivers are usually supported by a team which each team have a name and is identifiable…arrow_forward
- Consider the following relational database that represents books, publishers, and books published by publishers. 1) Does this database schema allow for a book to have more than one publisher? Explain why or why not using an example. 2) Does this database schema allow for a book to be currently out of print (not currently being published)? Explain why or why not using an example. 3) List three super keys of the books relation. 4) List all candidate keys of the books relation. 5) List all of the candidate keys of the publishes relation. 6) List all of the superkeys of the publishes relation. 7) Are there any attributes in considered logically non-atomic? Briefly explain why or why not. Assuming tuple (4,83,200,1990) into the publishes table failed. Why did this happen? What database principle was violated? 9) Does the SQL statement ( select title, author from books;) evaluate to a relation? Briefly explain. 10) one web site. the database that could be we have the tables above, inserting…arrow_forwardPlease written by computer source Part I - Modeling You are building a database for a comic book store customer management system. Given the database specification below, your job is to turn the specification into an ER diagram. Your notation must be consistent with the notation used in our lecture notes. You can use all constructs and notations discussed in our lecture notes and nothing else (i.e.. oven notations used in the textbooks but not in our lectures cannot be used). Your ER diagram should capture all the information and constraints in the specification but, at the same time, be minimal. It means that redundant entities, relationships, attributes and constraints should be avoided. You may use any drawing tool of your choice (for example, Dia, diagrams.net, Lucidchart, etc.) to draw your ER diagram. Hand drawings will not be accepted. Database Specification The database keeps information about comic books, customers, orders, inventory and reviews. First, the database should…arrow_forwardQUESTION 5 Consider the following relations in a University database: Student (SID, SName, Age, GPA) Course (CID, CName, Lecturer) Enrollment (SID, CID, Grade) SID is the primary key of Student, and CID is the primary key of Course (SID, CID) is the primary key of Enrollment, where SID is the foreign key referencing Student and CID is the foreign key referencing Course. CName is a unique field in the Course relation. Given the following SQL query: SELECT S.SName, E. Grade FROM Student S, Course C, Enrollment E WHERE S.SID = E.SID AND C.CID = E.CID AND C.CName = 'Database'; Show the initial query tree based on the conceptual evaluation strategy. Show the most efficient query tree after applying all the five steps of rule-based query tree optimization. (1) (2) Break up conjunctive selection into a cascade of selection operators; • Push down selection operators; . Convert cross-products into joins; Rearrange leaf nodes to execute the most restrictive selection operators first, Push down…arrow_forward
- Consider the E-R diagram for a university database as shown in Figure 1. What is NOT true about instructorand department?(a) A department may have many instructors.(b) An instructor may be associated with multiple departments.(c) An instructor may exist without being associated with any department.(d) none of the abovearrow_forwardDesign a Relational Database by Mapping the following Entity-Relationship Diagram (ERD) into Relational Model. You must clearly determine the PK of each relation. Also, you should show the links between FKs and PKs in your answer. SName Owner SHIP MOVEMENT PName HISTORY HOME PORT SHIP PORT Latitude (0,") VISITS (0.") Timestamp Longitude N N N (1.1) IN TYPE ON 1 1 Date Time StartDate VISITS Type EndDate Tonnage SHIP TYPE Name STATE/COUNTRY Name SEA/OCEAN/LAKE Hull Continentarrow_forwardConsider a “Digital Games” database with the following relations and attributes: Gamer: (UserID, Name, Age, Address, City, Favorite-Game, Rank) Game: (GameID, Name, Company, Release-Year, Version) Store: (StoreID, Name, Address, City) WhereToFind: (GameID, StoreID, Price) Category: (GameID, Category-Name) From the relational schemas illustrated above, use the different relational algebra operators and operations (ρ, σ, π, ⋂, ⋃, -, ⨝) discussed in class to build the following queries:1. List -by name and ID- each gamer who lives in the city of Chicago, with a rank of 5, and is aged between 15 (included) and 30 (included).2. List games -by name and release year- that are not in the “Action” category3. Since each game can fall within several categories, list games -by name and release year- that are in the “Action” category and also in the “Role-Playing” category4.…arrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningFundamentals of Information SystemsComputer ScienceISBN:9781305082168Author:Ralph Stair, George ReynoldsPublisher:Cengage LearningPrinciples of Information Systems (MindTap Course...Computer ScienceISBN:9781285867168Author:Ralph Stair, George ReynoldsPublisher:Cengage Learning
- Database 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 LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr