Question 2Figure 2 shows an ER schema for a database that may be used to keep track of Pizza orderingsystem. Map this schema into a relational schema, and specify all primary keys and foreignkeys.CityStreetLast_nameDelivery_date_timeHouse_numberOrder_date_timeFirst_nameTotal_priceOrder idemailCustomer_idOrder_Status(0,N)(1,1)(1,N)CustomerplacesOrder(1,1)Phone_numberhas(1, N)finalize(1,1)PriceNon_Pizza_idAmountNamehasNameAmountPizza|(1}1)Non_PizzaPrice(0,NPizza_id(0.N)(1,N)Employee_idNeedsNeedsEmployee(1,N)(1,N)ingredient_idUser_namePasswordIngredientsNamePriceER Diagram: Online Pizza Ordering System

Answer in step2 Note--since in E-R diagram cannot show foreign key so we are giving possible foreign…

Answered: Figure 2 shows an ER schema for a…

Figure 2 shows an ER schema for a database that may be used to keep track of Pizza ordering system. Map this schema into a relational schema, and specify all primary keys and foreign keys.

Database Systems: Design, Implementation, & Management

11th Edition

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter10: Transaction Management And Concurrency Control

Section: Chapter Questions

Problem 1P

See similar textbooks

Similar questions

Given the following relation R and its functional dependencies: R(workerNumber, repairNumber, workerName, machineNumber, spentTime, repairDate, machineName, workshopNumber, workshopName ) workerNumber à workerName repairNumber à repairDate, machineNumber machineNumber à machineName, workshopNumber workshopNumber à workshopName Explain the types of all the dependencies in the relation R. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.
Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNhmber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The primary keys are underlined. The referential integrity constraints are as follows: The column facID of relation Classes that references table Faculty, The column of stuID of relation Enrolls that references table Students, and The column classNumber of relation Enrolls that references table Classes. The following SQL statement is intended to define the table Students. However, this statement does not work correctly. CREAT TABLE Students( lastName VARCHAR(20), firstName VARCHAR(20), major VARCHAR(40) gpa DECIMAL(3,2), PRIMARY KEY(stuID)); However, this statement does not work correctly. Briefly explain why and list the corrected SQL statement.
Given the following relation R and its functional dependencies: R(workerNumber, repairNumber, workerName, machineNumber, spentTime, repairDate, machineName, workshopNumber, workshopName ) workerNumber à workerName repairNumber à repairDate, machineNumber machineNumber à machineName, workshopNumber workshopNumber à workshopName 1)Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table
Giving the following schema for a supermarket database, specify the domain constrains for each attribute. CUSTOMERS (CustomerID , First_Name, Last_Name) EMPLOYEES (EmployeeID, Name, Email, PhoneNo) INVOICES( InvoiceID, Amount_Paid, Invoice_Date)
3. Assume that relation PROJ of our example database above is horizontally fragmented as follows: PROJO PNOS P2 (PROJ) PROJ₂ = PNO P2 (PROJ) And, the relation ASG is horizontally fragmented as ASGO PNOS P2 (ASG) ASG₂ = σ "P2" <PNOS P3 (ASG) ASG=σPNO>P3 (ASG) 1) Transform the following query into a reduced query on fragments: and 2) Is it better than the generic query? And Why? or Why not? SELECT RESP, BUDGET FROM ASG, PROJ WHERE ASG.PNO PROJ.PNO AND PNAME="CAD/CAM" =
QUESTION 4 Consider the database schema below: Company (CompnayID: integer, CompnayName: String (unique), address) Department (DepartmentID: Integer, DepartmentName: String, CompanyID: Integer) Employee (ID:integer; name:String, DepartmentID:intger (not null)) Is it valid in the department relation, that two different departments to have the same value of CompanyID? No, violates domain constraints. No, violates key constraint. Yes. No, violates referential integrity constraint. No, violates entity Integrity constraint.
QUESTION 3 Consider the database schema below: Company (CompnayID: integer, CompnayName: String (unique), address) Department (DepartmentID: Integer, DepartmentName: String, CompanyID: Integer) Employee (ID:integer; name:String, DepartmentID:intger (not null)) Is it valid for an employee to work in two different departments? No, violates entity Integrity constraint No, violates key constraint. Yes. No, violates domain constraints. No, violates referential integrity constraint.
Consider a database that consists of the following relations.SUPpLIER(Sno. Sname)PART(Pno, Pname)PROJECT(Jno, Jname)SUPPLY(Sno, Pno, Jno)The database records information about suppliers, parts, and projectsandincludes a ternary relationship between suppliers, parts, and projects.Thisrelationship is a many-many-many relationships. Specify and execute thefollowingqueries using the relational algebraRetrieve the part numbers that are supplied to exactly two projects.b. Retrieve the names of suppliers who supply more than two parts toproject J11c. Retrieve the part numbers that are supplied by every supplier.d. Retrieve the project names that are supplied by supplier S1' only.e. Retrieve the names of suppliers who supply at least two different partseach to at least two different projects.
Consider the following relations in the relational schema for a database that keeps track of business trips of Sales Representatives in a sales office: salesperson(SSN, lName, startYear, deptNo) trip(tripID, SSN, fromCity, toCity, departureDate, returnDate) expense(tripID, accountNumber, amount) Part 1 Answer the Following 3 Questions. Places Your Answers in a Word Processor identify the primary key for each table. Identify the foreign keys in each table. Given the above foreign keys, list the order in which the tables could be successfully created. Write your Answers using a Word Processor Review the Relational Schema and other necessary material in this assignment to create the metadata document for the above relational schema. Write your Metadata using a Spreadsheet or a Word Processor. Submit the Final Version as a PDF Open up a text editor such as Visual Studio Code. Write the SQL SELECT Queries to respond to the following Information Requests Find…
QUESTION 6 Consider the database schema below: Company (CompnayID: integer, CompnayName: String (unique), address) Department (DepartmentID: Integer, DepartmentName: String, CompanyID: Integer) Employee (ID:integer; name:String, DepartmentID:intger (not null)) Is it valid in the employee relation to have the same value of DepartmentID for three different Employees?   No, violates Entity Integrity constraint   No, violates referential integrity constraint.   No, violates Key constraint.   Yes.   No, violated Domain Constraints
Consider the following relational database schema that contains information about employees and projects to which they are assigned. Emp (eid, lastname, gender, DOB) Assign (eid, pid, hours) Proj (pid, pname, budget, manager_eid)                                            Answer the following questions. (a) Find two superkeys in the Proj table — both of these two superkeys should NOT be same as the primary key. You should use appropriate set notation if the superkey contains multiple attributes. (b) Suppose the Emp and Proj tables have been created appropriately. Define (i.e., create) the Assign table in SQL (Just create only one table.) You should follow all the requirements discussed in the class. (c) Suppose in the Proj table a project has changed pid from P06 to P07. From the perspective of integrity constraints, how should this modification be handled?
DATABASE SYSTEMS 16.Consider a relation R = (A,B,C,D,E) with the following functional dependencies: {C,E → D,   D → B,   C → A} and identify a candidate key. None of them {C,E,D} {C,E,B} {C,E} 17.A join in which tuples from R1 that do not have matching values in the common attributes of R2 are also included in the result relation. R1 left outer join R2 R1 semi join R R1 equi join R2 R1 natural join R2 18.When two transactions that access the same database items have their operations interleaved in a way that makes the values of some database items incorrect this problem is call? none of them dirty read summary problem the lost update problem 19.Protect the database from inconsistencies and data loss Database recovery Database Transaction & Concurrency control Concurrency control Concurrency control & Database recovery 20.Let E1 and E2 be two entity types in an entity relationship diagram. E1 have all three simple single-valued attributes while E2 have two simple…

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning