Using the STUDENT table structure shown in Table 1, do the following:a. Write the relational schema and draw its dependency diagram. Identify alldependencies, including all transitive dependencies.b. Write the relational schema and draw the dependency diagram to meet the3NF requirements to the greatest practical extent possible. If you believe thatpractical considerations dictate using a 2NF structure, explain why yourdecision to retain 2NF is appropriate. If necessary, add or modify attributesto create appropriate determinants and to adhere to the naming conventions.c. Using the results of Problem b, draw the Crow's Foot ERD.TABLE 1: STUDENT INFOATTRIBUTESAMPLESAMPLESAMPLESAMPLESAMPLENAMEVALUEVALUEVALUEVALUEVALUESTU_NUM211343200128199876198648223456STU LNAMEStephanosSmith.JonesOrtizMcKulskiSTU_MAJORAccounting Accounting Marketing MarketingStatisticsDEPT CODEACCTАССTMKTGMKTGΜΑΤΗDEPT NAΜΕAccounting Accounting Marketing MarketingMathematicsDEPT PHOΝE43564378435643783420BusinessBusinessBusinessBusinessArts &COLLEGE_NAMEAdminAdminAdminAdminSciencesADVISOR_LNAME GrastrandGrastrandGentryTilleryChen ADVISOR OFFICE T201T201T228Т356Ј331ТorreТorreTorreTorreJonesADVISOR_BLDGBuildingBuildingBuildingBuildingBuildingADVISOR_PHONE 21152115212321593209STU_GPA3.872.782.313.453.58STU_HOURS754511711387STU_CLASSSophomore SeniorJuniorSeniorJunior

Using the STUDENT table structure shown in Table 1, do the following a. Write the relational schema and draw its dependency diagram. Identify all dependencies, including all transitive dependencies. b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions. c. Using the results of Problem b, draw the Crow's Foot ERD. TABLE 1: STUDENT INFO ATTRIBUTE SAMPLE SAMPLE VALUE SAMPLE VALUE SAMPLE SAMPLE NAME VALUE VALUE VALUE STU NUM 211343 200128 199876 198648 223456 STU LNAMΜΕ Stephanos Smith Jones Ortiz McKulski STU_MAJOR Accounting Accounting Marketing Marketing Statistics DEPT CODE ACCT ACCT MKTG MKTG МАТH DEPTNAMΕ Accounting Accounting Marketing Marketing Mathematics DEPT_PHONE | 4356 Business COLLEGE_NAME Admin 4378 Business Admin 4378 Business 3420 4356 Business Admin Arts & Admin Sciences ADVISOR_LNAME | Grastrand Grastrand Gentry Tillery Chen

Using the STUDENT table structure shown in Table 1, do the following a. Write the relational schema and draw its dependency diagram. Identify all dependencies, including all transitive dependencies. b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions. c. Using the results of Problem b, draw the Crow's Foot ERD. TABLE 1: STUDENT INFO ATTRIBUTE SAMPLE SAMPLE VALUE SAMPLE VALUE SAMPLE SAMPLE NAME VALUE VALUE VALUE STU NUM 211343 200128 199876 198648 223456 STU LNAMΜΕ Stephanos Smith Jones Ortiz McKulski STU_MAJOR Accounting Accounting Marketing Marketing Statistics DEPT CODE ACCT ACCT MKTG MKTG МАТH DEPTNAMΕ Accounting Accounting Marketing Marketing Mathematics DEPT_PHONE | 4356 Business COLLEGE_NAME Admin 4378 Business Admin 4378 Business 3420 4356 Business Admin Arts & Admin Sciences ADVISOR_LNAME | Grastrand Grastrand Gentry Tillery Chen

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

please help me answer all the questions in the picture. thank you
Solve with explanation
What is the definition of a transitive dependency? What is the rule for converting second normal form tables to third normal form tables?
Instructions: In this assignment, you will convert an Entity-Relationship diagram into a set of related tables. Given the following ERD, write the table definitions for the entities displayed below. You do not have to indicate data types nor “not null” constraints in this lab. Use the format TableName ( attribute, attribute…attribute). Designate primary keys with an underline and foreign keys with a broken underline. To illustrate, the Regions and Countries table definitions have been created for you below the diagram. Type your answers in this document and upload it to the appropriate Blackboard assignment link by Monday at 11:59 p.m. ET of this module/week. Note that you should read the “Table Conversions Explained” document in your Reading & Study folder. I created it to help you better understand how ERDs will ultimately be transformed into real tables. ERD for the Hourglass timekeeping database
Use the technique of normalization to validate the structure of your relational schema. Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional dependencies between attributes in each relation. Note, if any of your relations are not in 3NF, this may indicate that your ER model is structurally incorrect or that you have introduced errors in the process of deriving relations from your model.
Please draw an relational ERD with multiplicities and all the attributes based on following description: ( Please identify the primary key in each entity.) An Adult Education Department runs various courses during the daytime and evenings, and at different times of the year. For example, ‘Spanish level 1’ is offered on Monday mornings, Monday evenings or Wednesday evenings, and runs over 25 weeks from October to March. On the other hand, ‘Introduction to Digging Up Your Ancestors’ only runs for 8 weeks, but is offered on Tuesday or Wednesday evenings from October to December, January to March, and April to June, with an optional field week in August. There is always a maximum number of places for each course offering, which is dependent on the individual teacher. For example, ‘Spanish level 1’ on Monday evenings may be limited to 20 places, but on Wednesday evenings the limit may be 25. Each course offering is only taken by one teacher, however teacher may take…
The dependency diagram indicates that authors are paid royalty for each book they write for a publisher.The amount of royalty can vary by author,by book ,and by edition of the book. a.Based on the dependency diagram,create a database whose tables are atleast in 2NF showing the dependency diagram for each table. b.Create a database whose tables are atleast in 3NF,showing the dependency diagram for each table.
Create relational schemas for the ER diagram given below. Indicate clearly the primary and foreign keys of each relation. Туре Address NoPersons Area No No M House Has Room For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac).
Consider the follow relations’ schemas to answer the following questions: Student (sid, name, level, address) Book(bid, title, author) BorrowedBook(sid, bid, date) 1) Assume that, there are 10,000 Student records stored on 1,000 pages/blocks, 50,000 Book records stored on 5,000 pages, 300,000 BorrowedBook records stored on 15,000 pages, and there are 500 different authors. Find out what is the record size for each of the previous relation?, and what is the blocking factor for each relation?
Consider the following relational schema of Dept_Location relation Dept Location(dno, location) We want to retrieve the dno and the number of locations of for each dno. SELECT FROM Dept_Location group by
Please find the relational schema and the Sql Statement
Consider the following relation named Trip. Trip The functional dependencies are as follows: (TripID, TripName, StartLocation, State, Distance, MaxGrpSize, Type, SeasonNum, Season TripID, SeasonNum → TripID → A. SeasonNum →→ Season C. [TripName, StartLocation, State, Distance, MaxGrpSize, Type The tables that are in third normal form are: [TripName, StartLocation, State, Distance, MaxGrpSize, Type, Season Trip TripID, TripName, StartLocation, State, Distance, MaxGrpSize, Type) Season (Season Num, Season), TripSeason (TripID, SeasonNum) B. Trip (TripID, TripName, StartLocation, State, Distance, MaxGrpSize, Type) Season (SeasonNum, Season) Trip TripID, TripName, StartLocation, State, Distance, MaxGrpSize, Type) Season (TripID, SeasonNum, Season) D. None of the above