Consider the following set of requirements for a university database that is used to keep track ofstudents' of student enrollments in classes and students' final grades:a. The university is organized into colleges (COLLEGE), and each college has a uniquename (CName), a main office (COffice) and phone (CPhone), andparticular facultymember who is dean of the college. Each college administers a number of academicdepartments (DEPT). Each department has a unique name (RName), a unique codenumber (RCode), a main office (ROffice) and phone (RPhone), and a particular facultymember who chairs the department. We keep track of the start date (CStartDate) whenthat faculty member began chairing the department.b. A department offers a number of courses (COURSE), each of which has a unique coursename (COName), a unique code number (CCode), a course level (Level: this can be codedas 1 for freshman level, 2 for sophomore, 3 for junior, 4 for senior, 5 for MS level, and 6for PhD level), a course credit hours (Credits), and a course description (CDesc). Thedatabase also keeps track of instructors (INSTRUCTOR); and each instructor has aunique identifier (Id), name (IName), office (IQffice), phone (IPhone), and rank (Rank);in addition, each instructor works for one primary academic departmentc. The database will keep student data (STUDENT) and stores each student's name(SName, composed of first name (FName), middle name (MName), last name (LName)),student id (Sid, unique for every student), address (Addr), phone (Phone), major code(Major), and date of birth (ReB). A student is assigned to one primary academicdepartment. It is required to keep track of the student's grades in each section the studenthas completed.d. Courses are offered as sections (SECTION). Each section is related to a single course anda single instructor and has a unique section identifier (Secld). A section also has a sectionnumber (ŞecNo; this is coded as 1, 2, 3, ... for multiple sections offered during the samesemester/year), semester (Sem), year (Year), classroom (CReom: this is coded as acombination of building code (Bldg) and room number (RoomNe) within the building), and days/times (DaysTime: for example, 'MWF 9am-9.50am' or TR 3.30pm-5.20pm'-restricted to only allowed days/time values). (Note: The database will keep track of allthe sections offered for the past several years, in addition to the current offerings. TheSecld is unique for all sections, not just the sections for a particular semester.) Thedatabase keeps track of the students in each section, and the grade is recorded whenavailable (this is a many-to-many relationship between students and sections). A sectionmust have at least five students.Draw an ER diagram for this application. Specify key attributes of each entity type and structuralconstraints on each relationship type. Note any unspecified requirements, and make appropriateassumptions to make the specification complete.

Definition of ER Diagram : Entity Relationship Diagram or ER Diagram describes interrelated things…

and days/times (QaysTime; for example, 'MWF 9am-9.50am' or TR 3.30pm-5.20pm'- restricted to only allowed days/time values). (Note: The database will keep track of all the sections offered for the past several years, in addition to the current offerings. The Secld is unique for all sections, not just the sections for a particular semester.) The database keeps track of the students in each section, and the grade is recorded when available (this is a many-to-many relationship between students and sections). A section must have at least five students. Draw an ER diagram for this application. Specify key attributes of each entity type and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

and days/times (QaysTime; for example, 'MWF 9am-9.50am' or TR 3.30pm-5.20pm'- restricted to only allowed days/time values). (Note: The database will keep track of all the sections offered for the past several years, in addition to the current offerings. The Secld is unique for all sections, not just the sections for a particular semester.) The database keeps track of the students in each section, and the grade is recorded when available (this is a many-to-many relationship between students and sections). A section must have at least five students. Draw an ER diagram for this application. Specify key attributes of each entity type and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter9: Database Design

Section: Chapter Questions

Problem 5P

See similar textbooks

Similar questions

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,…
Consider the following business rule:An employee can manage multiple customers. A customer can be managed by multiple employees. How does this business rule(s) affect the database design? a.) The business rules define that there isn't a need for a relationship between the EMPLOYEE and CUSTOMER entities, as the relationship isn't a strong one. b.) The business rules define that there is a relationship between EMPLOYEE and CUSTOMER, and a 1:M relationship between those two entities. c.) The business rules define that there is a relationship between EMPLOYEE and CUSTOMER, and an M:N relationship between those two entities requiring a bridge table. d.) The business rules define that there is a relationship between EMPLOYEE and CUSTOMER, and a 1:1 relationship between those two entities.
Develop an EER model for the following situation using EER 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 DVD, projector, etc.), not the individual piece of equipment. Tracking of equipment is outside of the scope of this project. • Computer is identified by CType ID and has attributes of Computer Type and Type Description, Disk Capacity, and Processor Speed. Please note: as with Media Type, we are tracking…
3- Consider a database for a university system that needs to store information about students,courses, and instructors. Each student can enroll in multiple courses, and each course can havemultiple instructors. Normalize the following schema to Third Normal Form (3NF), and showyour steps in between (1NF, 2NF).Students (StudentID, FirstName, LastName, CourseID, InstructorID)Courses (CourseID, CourseName, InstructorID)Instructors (InstructorID, FirstName, LastName)4- To keep track of office furniture, computers, printers, and other office equipment, the FOUNDITCompany uses the table structure shown below.a. Given that information, write the relational schema and draw the dependency diagram.Make sure that you label the transitive and/or partial dependencies.b. Write the relational schema and create a set of dependency diagrams that meet 3NFrequirements. Rename attributes to meet the naming conventions, and create newentities and attributes as necessary.
Consider the following relational database for the Super Baseball League. It keeps track of teams in the league, coaches and players on the teams, work experience of the coaches, bats belonging to each team, and which players have played on which teams. Note the following facts about this environment: • Thedatabasekeepstrackofthehistoryofalltheteams that each player has played on and all the players who have played on each team. • The database only keeps track of the current team that a coach works for. • Team number, team name, and player number are each unique attribute across the league. • Coach name is unique only within a team (and we assume that a team cannot have two coaches of the same name). • Serial number (for bats) is unique only within a team. • In the Affiliation table, the Years attribute indicates the number of years that a player played on a team; the batting average is for the years that a player played on a team. Write SQL SELECT commands to answer the following…
Consider the following relational database for the Super Baseball League. It keeps track of teams in the league, coaches and players on the teams, work experience of the coaches, bats belonging to each team, and which players have played on which teams. Note the following facts about this environment: • Thedatabasekeepstrackofthehistoryofalltheteams that each player has played on and all the players who have played on each team. • The database only keeps track of the current team that a coach works for. • Team number, team name, and player number are each unique attribute across the league. • Coach name is unique only within a team (and we assume that a team cannot have two coaches of the same name). • Serial number (for bats) is unique only within a team. • In the Affiliation table, the Years attribute indicates the number of years that a player played on a team; the batting average is for the years that a player played on a team. Write SQL SELECT commands to answer the following…
5.14 - Consider the following six relations for an order-processing database application in a company: CUSTOMER (Cust#, Cname, City) ORDER (Order#, Odate, Cust#, Ord_Amt) ORDER_ITEM (Order#, Item#, Qty) ITEM (Item#, Unit_price) SHIPMENT (Order#, Warehouse#, Ship_date) WAREHOUSE (Warehouse#, City) Here, Ord_Amt refers to total dollar amount of an order; Odate is the date the order was placed; Ship_date is the date an order (or part of an order) is shipped from the warehouse. Assume that an order can be shipped from several warehouses. Specify the foreign keys for this schema, stating any assumptions you make. What other constraints can you think of for this database?
Consider the following 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, a starting date, an ending date, and a budget. • Graduate students have SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.). • Each project is managed by one professor (known as the project’s principal investigator). • Each project is worked on by one or more professors (known as the project’s coinvestigators). • Professors can manage and/or work on multiple projects. • Each project is worked on by one or more graduate students (known as the project’s research assistants). • When graduate 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 (potentially different) supervisor for each one. • Departments have a department number, a department name, and a main office. • Departments…
Construct the ER diagram for the NHL database using the Crows Foot notation Suppose you are given the following requirements for a simple database for the National Hockey League (NHL):• the NHL has many teams,• each team has a name, a city, a coach, a captain, and a set of players,• each player belongs to only one team,• each player has a name, a position (such as left wing or goalie), a skill level, and a setof injury records,• a team captain is also a player,• a game is played between two teams (referred to as host_team and guest_team) andhas a date (such as May 11th, 2017) and a score (such as 4 to 2).
Consider the following 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 starting date, an ending date, and a budget. ▪ Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.). ▪ Each project is managed by one professor (known as the project’s principal investigator). ▪ Each project is worked on by one or more professors (known as the project’s co-investigators). ▪ Professors can manage and/or work on multiple projects. ▪ Each project is worked on by one or more graduate students (known as the project’s research assistants). ▪ When graduate 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 (potentially different) supervisor for each one. ▪ Departments have a department number, a department…
Following on your BIG break to make a database for an… organization that organizes table tennis tournaments (also known as “Tourney”s) at their premises on behalf of clubs, you’ve written down the business rules as follows: One Match belongs to One Tourney, and not none. One Tourney has Many Matches, at least one and a maximum of 128. One Match takes place on One Table, and not none. One Table is used in Many Matches, and possibly none. One Match belongs to one Category, and not none. One Category appears in Many Matches, and possibly none and a max of 32. One Match involves Many Players, minimum 2 and maximum 4. One Player plays Many Matches, and possibly none. You have also been given a small sample of their records (which is not in the least in relational format), which may prove useful. It is below. Match Number Tourney Tourney Start Date Day Time Slot In Day Player Names(Team 1) Player Names(Team 2) Player IDs (Team 1) Player IDs(Team 2)…
Consider a MOVIE database in which data is recorded about the movie industry. The data requirements are summarized as follows: -- Each movie is identified by title and year of release. Each movie has a length in minutes and a rating (e.g., G, PG, or R). Each has a production company, and each is classified under one or more genres(such as horror, action, drama, and so forth). Each movie has one or more directors and one or more actors appear in it. Each movie also has a plot outline. Finally, each movie has zero or more quotable quotes, each of which is spoken by a particular actor appearing in the movie. An actor may have multiple quotes from one movie. -- Actors are identified by name and date of birth and appear in one or more movies. Each actor has a role in the movie, i.e., the character he or she plays. -- Directors are also identified by name and date of birth and direct one or more movies. -- Production companies are identified by name and each has an address. A production…

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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