Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Expert Solution & Answer
Chapter 5, Problem 4P
Explanation of Solution
Crow’s Foot ER diagram for Tiny College:
The following data model shows the solution for the given question.
Explanation:
- In the above data model, the “Employee” is the supertype entity of “Staff”, “Admin” and “Professor” subtypes and the primary key of “Employee” entity is “Emp_No”. The primary and foreign key of these subtypes are “Emp_No”. Another foreign key of “Professor” entity is “Dept_Code”.
- The overlapping constraint (o) is used as a category symbol. Here the “Employee” supertype entity has optional subtype entity.
- The “Student” table contains the details of students and every student can get advices from the “Staff” table...
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
In Chapter 4, you saw the creation of the Tiny College database design, which reflected such business rules as "a professor may advise many students" and "a professor may chair one department." Modify the design shown in Figure 4.35 to include these business rules:
An employee could be staff, a professor, or an administrator.
A professor may also be an administrator.
Staff employees have a work-level classification, such as Level I or Level II.
Only professors can chair a department. A department is chaired by only one professor.
Only professors can serve as the dean of a college. Each of the university's colleges is served by one dean.
A professor can teach many classes.
Administrators have a position title.
Given that information, create the complete ERD that contains all primary keys, foreign keys, and main attributes.
In Chapter 4, you saw the creation of the Tiny College database design. That design reflected such business rules as “a professor may advise many students” and “a professor may chair one department.” Modify the design shown in Figure 4.36 to include these business rules:
An employee could be staff or a professor or an administrator.
A professor may also be an administrator.
Staff employees have a work level classification, such a Level I and Level II.
Only professors can chair a department. A department is chaired by only one professor.
Only professors can serve as the dean of a college. Each of the university’s colleges is served by one dean.
A professor can teach many classes.
Administrators have a position title.
Given that information, create the complete ERD containing all primary keys, foreign keys, and main attributes.
**Link to previous source acceptable
Please written by computer source
1. Let's say you're a database designer for a logistics company. Your task is to design a databaseto store information about the company's trucks, drivers, shipments, and customers. (1) Each truck has a unique id, information about its make and model, and its capacity.Each shipment has a unique id, as well as information about its origin, destination, andweight. Each customer has a unique id and information about their name, address, andphone number. Each driver has a unique id and information about their name, licensenumber, and level of experience.(2) A truck can transport one or more shipments, and each shipment can be transportedby one or more trucks. A customer can have one or more shipments, and each shipmentcan belong to one customer. a driver can drive one or more trucks, and each truck can bedriven by one or more drivers.
Chapter 5 Solutions
Database Systems: Design, Implementation, & Management
Ch. 5 - Prob. 1RQCh. 5 - What kinds of data would you store in an entity...Ch. 5 - Prob. 3RQCh. 5 - What is a subtype discriminator? Give an example...Ch. 5 - Prob. 5RQCh. 5 - What is the difference between partial...Ch. 5 - Prob. 7RQCh. 5 - According to the data model, is it required that...Ch. 5 - Prob. 9RQCh. 5 - Prob. 10RQ
Ch. 5 - Prob. 11RQCh. 5 - Prob. 12RQCh. 5 - Prob. 13RQCh. 5 - When implementing a 1:1 relationship, where should...Ch. 5 - What is time-variant data, and how would you deal...Ch. 5 - Prob. 16RQCh. 5 - Prob. 1PCh. 5 - Prob. 2PCh. 5 - Prob. 3PCh. 5 - Prob. 4PCh. 5 - Prob. 5PCh. 5 - Prob. 6PCh. 5 - Prob. 7PCh. 5 - Prob. 8CCh. 5 - Prob. 9CCh. 5 - Prob. 10C
Knowledge Booster
Similar questions
- Draw the ER diagram based on the following specification. A tutorial school is using a database to manage information about teachers, courses, and students. Each teacher is identified by staff_id, has a name, an email address, and a phone number. Each student is identified by student_id, has a name, an email address, and a registration date. Each course is identified by course_id, year, and semester. In addition, each course has a name. Each course is associated with multiple lessons. A lesson has a lesson number, a location, a date, a start-time, and an end-time. Lesson numbers are sequentially numbers, starting from 1, generated separately for each course. Each teacher teaches at least one course. Each student takes at least one course. Each course is taught by one teacher. Each course can be taken by many students.arrow_forwardDraw an ER diagram of the following: You are going to set up a database company that builds a product for art galleries. The core of this product is a database that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, we need to record its unique title, the year it was made, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price. An artist may paint many pieces of artwork and a piece of artwork is done by one artist. Pieces of artwork are also classified into groups of various kinds (for example, portraits, works by Picasso, or works of the 19th century). A given piece may belong to more than one group. Each group is identified by a name that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent…arrow_forwardCreate an ER diagram for the following requirements:You are asked to design a database to store music album information. A simplified set of requirementsare provided below.- A music album has a title, year of release, total running time, one or more artists, and list ofsongs.- Each song has a lead artist and may have a list of additional artists. Each song has a list of otherassociated people such as mixer, mix engineer, keyboards, guitar, etc. Each song has a genre,running time, and lyrics. Each song can be associated with many albums, but needs to beassociated with at least one.- Each person has a first name, last name, and nickname.arrow_forward
- Design and draw an ER diagram that captures the information about the university. Consider thefollowing information about a university database: Professors have an SSN, a name, an age, a rank,and a research specialty. Projects have a project number, a sponsor name (e.g., NSF), a startingdate, an ending date, and a budget. Graduate students have an SSN, a name, an age, and a degreeprogram (e.g., M.S. or Ph.D.). Each project is managed by one professor (known as the project’sprincipal investigator). Each project is worked on by one or more professors (known as theproject’s co-investigators). Professors can manage and/or work on multiple projects. Each projectis worked on by one or more graduate students (known as the project’s research assistants). Whengraduate students work on a project, a professor must supervise their work on the project.Graduate students can work on multiple projects, in which case they will have a (potentiallydifferent) supervisor for each one. Departments have a…arrow_forwardSuppose you are asked to design a club database system based on the following information. Each student has a unique student id, a name, and an email; each club has a unique club id, a name, a contact telephone number, and has exactly one student as its president. Each student can serve as a president in at most one of the clubs, although he/she can be the members of several clubs. Clubs organize activities and students can participate in any of them. Each activity is described by a unique activity id, a place, a date, a time and those clubs that organize it. If an activity is organized by more than one club, different clubs might contribute different activity fees.QuestionDraw an E-R diagram for the system according to the above description.arrow_forwardA university wants to develop a database system to manage student enrollment and course registration. They need an ERD to represent the key entities and their relationships. Entities: Student: Each student has a unique student ID, a name, a date of birth, and an email address. Course: Each course has a unique course code, a title, a description, and the number of credits. Enrollment: Each enrollment represents a student's registration in a specific course for a particular semester. It includes information about the grade received for that course. Professor: Each professor has a unique professor ID, a name, and an email address. Professors teach one or more courses. Create an ERD that represents the relationships between these entities. Include Entity name, attributes, identify Primary and Foreign key and crow foot relationships.arrow_forward
- Question : Draw the EER diagram that describes the following scenario: ( PLEASE EXPLAIN STEP BY STEP AND IN DETAIL) We want to design a database for keeping track of information about votes taken is the PAKISTAN PROVISIONS in the current administration. They include the PARLIAMENT and the SENATE. A MINISTER in the PAKISTAN is either a Parliament Member or a Senator depending on to which body they are elected to. The database needs to keep track of each PAKISTAN CITY’s Name (e.g., ‘Lahore’, ‘Karachi’, ‘Multan’) and include the Region of the country to which the city belongs (whose domain is {‘Punjab’, ‘Sind’, ‘KPK’, ‘Baluchistan’}) and its population in the 170 million. Each MINISTER is described by his or her Name, plus the District represented, the Start_date when the parliament was first elected, and the political Party to which he or she belongs (whose domain is {‘PPP’, ‘PTI’, ‘PML(N)’, ‘PML(Q)’, ‘ANP’, ‘MQM’, ‘Independent’, ‘Other’}). A city can have a number of parliament…arrow_forwardDraw ER (or EER) diagram: A conceptual database design for a chain of furniture stores is needed. The following is a summary of the database requirements: a) The chain has many different stores in various cities and states. Each STORE has unique StoreNumber, plus an Address(made up of Street, City, State, ZipCode), and a StoreManager (who is an employee). Each store is organized into a number of departments. b) Each DEPARTMENT contains certain types of furniture (such as ‘dining’, ‘living room’, ‘bedroom’, ‘office’, etc.), and has a DeptName that is unique within each store; it also has a DeptManager (who is an employee), and a number of SalesPersons (who are employees). c) The company employs many employees. Each EMPLOYEE has a unique SSN, plus a Name (made up of FirstName, Minit, LastName), a job (e.g. ‘store manager’, ‘dept manager’, ‘sales associate’, ‘sales person’, etc.), plus DateOfBirth, Sex, Salary, and CommissionRate. Each employee works for a particular store. Design an ER…arrow_forwardThese questions are in sequence and the previous question needs to be referred to answer the next question: This is the previous question that was askedQuestion 1(question to be answered at the end): Create the Entity-Relationship (ER) Diagram for the following scenario: We want to maintain a database for a Faculty within a multi-campus regional university. The faculty has a number of schools and each school is characterized by its school-code which is unique, name, and campus location. Schools employ professors who are characterized by their unique staff-id, name and the school they work for. A school offers courses that are characterized by their course-code which is unique, course name and the school. There are subjects offered within the faculty and these arecharacterized by unique subject-code, title and the school that offers them. Students enroll into courses that are characterized by student-id which is unique, name, address and phone.The following business rules apply• A…arrow_forward
- 1. Draw an E/R diagram for a University database. This database should contain information about the university's students, departments, professors, teaching assistants (also students of the university), courses offered by a department, which students are enrolled in what courses, which professors are teaching which courses, student letter grades. You may include any other information deemed appropriate. • Some restrictions apply as listed below| 1. A Professor only works in at most one department. 2. Students and professors have been assigned unique email id's 3. A course is only taught by one professor and offered by one department. 4. A course is uniquely identified by the course number, section number and semester. Example: CSCE520-001 Spring21 5. A course has at most one TA.arrow_forwardDesign an Entity-Relationship diagram for the below environment. determine 1-Total participation.2-Partial participation Consider a MAIL_ORDER database in which employees take orders for parts from customers. The data requirements are summarized as follows: o The mail order company has employees, each identified by a unique employee number, first and last name, and Zip Code. o Each customer of the company is identified by a unique customer num, first and last name, and Zip Code. o Each part is identified by a unique part number, a part name, price, and quantity. o Each order placed by a customer is taken by an employee and is given a unique order number. Each order contains specified quantities of one or more parts. Each order has a date of receipt as well as an expected ship date. The actual ship date is also recorded.arrow_forwardPart 3 5. Database design often involves decisions about the storage of attributes. For example a Telephone Number can be stored as a one attribute or split into three attributes (one for each of the three hyphen-delineated groups of numbers in a Telephone Number (XXX)-XXX-XXXX, I.e (123)-456-7890). However, Telephone Number is usually stored in one attribute. The decision is usually based on how the database will be used. This exercise asks you to think about specific situations where dividing the Telephone Number is useful. List and describe the different situations, give examples.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning