Mr Lim owns a restaurant for which he has hired several chefs. He wishes to design a database to keep track of his staff. Draw an Entity relationship diagram (ERD) for this database. The database needs to store information about the following: Chef: Each chef has a stafflD, a name and is assigned to a specific department. Some chefs are supervisor. A supervisor normally supervises several chefs. • Department: Mr Lim's restaurant has several departments. Each department has a unique name. Each department prepares one or more dishes and has at least one chef working on it. Dishes: Each dish has a unique name and is prepared in one or more departments.

Mr Lim owns a restaurant for which he has hired several chefs. He wishes to design a database to keep track of his staff. Draw an Entity relationship diagram (ERD) for this database. The database needs to store information about the following: Chef: Each chef has a stafflD, a name and is assigned to a specific department. Some chefs are supervisor. A supervisor normally supervises several chefs. • Department: Mr Lim's restaurant has several departments. Each department has a unique name. Each department prepares one or more dishes and has at least one chef working on it. Dishes: Each dish has a unique name and is prepared in one or more departments.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 3TD

See similar textbooks

Similar questions

Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNumber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The column facID of relation Classes that references the table Faculty, The column StuID of relation Enrolls that references table Students, and The column classNunber of relation Enrolls that references table Classes. The following SQL statement is intended to add a record to the table Students. Assume that the entered value for each column is appropriate. In other words, there is no domain constraint violation. INSERT INTO Students(stuID, lastName, firstName, major) VALUES('950555111', 'Anna' 'Smith', 'BSIT'); However, this statement does not work correctly. Briefly explain why and list the correct SQL statement.
I need help with the following Consider a STUDENT relation in a UNIVERSITY database with the followingattributes (Name, Ssn, Local_phone, Address, Cell_phone, Age, Gpa). Note thatthe cell phone may be from a different city and state (or province) from thelocal phone. A possible tuple of the relation is shown below:Name Ssn Local_phone Address Cell_phone Age GpaGeorge Shaw 123-45-6789 555-1234 123 Main St., 555-4321 19 3.75William Edwards Anytown, CA 94539a. Identify the critical missing information from the Local_phone andCell_phone attributes. (Hint: How do you call someone who lives in a dif ferent state or province?)b. Would you store this additional information in the Local_phone andCell_phone attributes or add new attributes to the schema for STUDENT?c. Consider the Name attribute. What are the advantages and disadvantagesof splitting this field from one attribute into three attributes (first name,middle name, and last name)?d. What general guideline would you recommend for…
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.
Suppose that the initial conceptual design of the Car Rental database consisted of just a single relation as follows:Rental(VehicleID, CustomerID, DateOut(PK), DateReturned, VehicleClass, RentalRate, CustomerFName, CustomerLName, Balance) Normalize the Rental relation to 2NF and 3NF
Given the database schema with the following relations: STUDENT (id_stud, name_stud) LECTURER (id_lect, name_lect) SUBJECT (id_subject, id_lect, name_subject, credits, id_room) ROOM (id_room, no_room) ENROLL (id_stud, id_subject) Create an algebraic relation for: a. Displays all names of lecturers who teach the subject with a total of 4 credits b. Displays all no_room with the student name "Eko" c. Displays all data in the form of student id, student name, name of the course followed and the name of the lecturer who taught the subject name "Database" From the database schema and the questions above, create an appropriate SQL query for each of these statements above.
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.
Develop an EER model for the following situation using the traditional EER notation, the Visio notation, or the subtypes inside supertypes notation, as specified by your instructor: An international school of technology has hired you to create a database management system to assist in scheduling classes. After several interviews with the president, you have come up with the following list of entities, attributes, and initial business rules: • Room is identified by Building ID and Room No and also has a Capacity. A room can be either a lab or a classroom. If it is a classroom, it has an additional attribute called Board Type. • Media is identified by MType ID and has attributes of Media Type and Type Description. Note: Here we are tracking type of media (such as a VCR, projector, etc.), not the individual piece of equipment. Tracking of equipment is outside the scope of this project. • Computer is identified by CType ID and has attributes Computer Type, Type Description,…
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
For the database relations defined in the attachments, write a tuple-based CHECK constraints so that: -> No ship can be in battle before it is launched. Since the Battles table only has the BATTLE name (and not the SHIP name), the Outcomes and Ships table will need to be used. Other websites found the wrong answer so don't copy them. Thank you
Draw an ER diagram and then map it into a relational schema(database) for the following scenario.• For each MEMBER we keep track of the unique MemdID, a well as Name, Zip, and the Datethe membership was paid.• For each MEMBERSHIP type we keep track of the unique Mid, as well as MName and Price• For each PASS CATEGORY we keep track of the unique PassCatID, as well as PCName andPrice• For each ONE DAY PASS we keep track of the unique PassID and Date• For each MERCHANDISE item we keep track of the unique MrchID, as well as Name andPrice• For each sale TRANSACTION we keep track of the unique Tid and Date• Each member pays for exactly one membership type; each membership type has at leastone member but can have many members.• Each member can buy many day passes but does not have to buy any, each day pass wasbought by exactly one member• Each day pass belongs to exactly one pass category; a pass category can have manyindividual day passes issued for it but does not have to have any. • Each…
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.
Assume that you have to design a databaseto manage books issued by your universitylibrary. Attributes for a book include bookidentification number, book title, author,ISBN, publisher, edition, and stock. Identifythe primary key for this entity. What attributes would be associated with other entitiesand used as foreign keys?

SEE MORE QUESTIONS

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

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

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

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

SEE MORE TEXTBOOKS