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 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

Related questions

Q: Display the list of students who are involved in the IT club Display the details of all the…

A: First get the studid from Club table and then get those Student details This can be done using inner…

Q: Using the Bowlers and Bowler_Scores tables, list the BowlerID, bowler name, and raw score for each…

A: The given problem requires us to find the BowlerID, bowler name, and raw score for each bowler who…

Q: nt database. The primary keys are highlighted in bold. Student (studNo, studName, address,…

A: Below is part of a Student database. The primary keys are highlighted in bold. Student (studNo,…

Q: Cust_id (NOT NULL) Txn_date (Not NULL) Txn_type (NOT NULL) Txn_amount (NOT NULL) 1 2020-01-21…

A: The CREATE TABLE statement is used to create a new table. Syntax: CREATE TABLE table_name (column1…

Q: Make a list of how many albums each artist has released. Only include artists with more than 5…

A: To create a list of artists with more than 5 albums, we need to query the database tables and count…

Q: Create the following table as part of a Hilton Hotel Business database: a. Hotel (hotelNo,…

A: Solution: Relationship Diagram:

Q: g Make sure to use oracle sql command and add foreign keys The faculty and students (we refer to…

A: The given requirements are related to the creation of data model using details provided for the…

Q: Task 12: List the item ID, description, and category for each pair of items that are in the same…

A: Creating table ITEMS: create table ITEMS(item_id varchar2(5) Primary Key,Description…

Q: Using the Henry Books database,update the paperback field for the book titled Black Houseto Y. You…

A: “Since you have posted a question with multiple sub parts, we will provide the solution only to the…

Q: The Car Maintenance team wants to update the price of Oil Change to 75. In addition, they will no…

A: We need to write the MYSQL query to update the price of Oil Change to 75 and remove the maintenance…

Q: access databases Normalization Exercise Represent this table as a collection of tables in 3NF:…

A: The key for the given relation is (StudentNumber, CourseNumber) The partial functional dependency…

Q: The Car Maintenance team also wants to store the actual maintenance operations in the database. The…

A: DELETE CASCADE: - When we constitute a foreign key using this option, it deletes the referencing…

Q: Note: I am asking you to create a table in a database. If you already have a database, that's fine,…

A: The objective of the question is to create a SQL table with specific data types and constraints. The…

Q: The Driver Relationship team wants to arrange workshops and education materials to the drivers.…

A: The Driver Relationship team wants to arrange workshops and education materials to the drivers.…

Q: Below is part of a Student database. The primary keys are highlighted in bold. Student (studNo,…

A: Given to write a PL/SQL PROCEDURE that will list the project names given the course ID. The below…

Q: Enclosure Number ENCLOSURE Date Type Location Size Built Animal Animal Number Species Name Gender…

A: Relationship diagram for the central zoo database is drawn below

Q: Please design a Task durations and a Dependencies table, and also calculate the Task durations and…

A: Given,Design a Task durations and a Dependencies table, and also calculate the Task durations and…

Q: Note: I am asking you to create a table in a database. If you already have a database, that's fine,…

A: The objective of the question is to create a SQL table with specific columns, data types, and…

Q: Display CustomerName and Status as 'Q2 for every customer who was born during the 2nd quarter of…

A: The given problem is related to database SQL query. The problem has provided the msCustomer table…

Q: How many patients in the database have been diagnosed with acute bronchitis (ICD9Code = 466) and…

A: Count Query :- SELECT COUNT(column_name) FROM table_name WHERE condition; Groupby Query :-…

Q: The Solmaris database does not include a field for service fees. In which table would you place the…

A: If there has to be a column/field which says about the amount charged for the services, then, such a…

Q: . Using the Henry Books database, list the book code and title of every book that has the type SFI…

A: In this question we have to write a query for using a Henry book database for the given queries…

Q: Using the Winners table from Figure 12-2, write a parameter query that selects theActor and Actress…

A: A query that has a parameter marker instead of a value of criteria is called as parameter query. The…

Q: What is the average distance and average maximum group size for each type of trip?

A: "SELECT" command: The "SELECT" command is used to retrieve data in a database. Syntax for selecting…

Q: Using the World database, how many regions are in the continent of Antarctica? Display this number…

A: SQL is the language that is used to communicate with the database. With the help of SQL, data can be…

Concept explainers

SQL

SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.

Queries

A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.

Question

Consider the following set of requirements for a university database that is used to keep track of
students' of student enrollments in classes and students' final grades:
a. The university is organized into colleges (COLLEGE), and each college has a unique
name (CName), a main office (COffice) and phone (CPhone), and
particular faculty
member who is dean of the college. Each college administers a number of academic
departments (DEPT). Each department has a unique name (RName), a unique code
number (RCode), a main office (ROffice) and phone (RPhone), and a particular faculty
member who chairs the department. We keep track of the start date (CStartDate) when
that faculty member began chairing the department.
b. A department offers a number of courses (COURSE), each of which has a unique course
name (COName), a unique code number (CCode), a course level (Level: this can be coded
as 1 for freshman level, 2 for sophomore, 3 for junior, 4 for senior, 5 for MS level, and 6
for PhD level), a course credit hours (Credits), and a course description (CDesc). The
database also keeps track of instructors (INSTRUCTOR); and each instructor has a
unique identifier (Id), name (IName), office (IQffice), phone (IPhone), and rank (Rank);
in addition, each instructor works for one primary academic department
c. 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 academic
department. It is required to keep track of the student's grades in each section the student
has completed.
d. Courses are offered as sections (SECTION). Each section is related to a single course and
a single instructor and has a unique section identifier (Secld). A section also has a section
number (ŞecNo; this is coded as 1, 2, 3, ... for multiple sections offered during the same
semester/year), semester (Sem), year (Year), classroom (CReom: this is coded as a
combination 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 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.

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Step 3

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 1 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

From the database scheme write the query(all tables are empty) 1) Insert your name and your phone as a new tenant 2) Delete your phone number from the table tenant. 3) Create a new contract lease for you. 4) Delete the contract that you created. 5) List the city name, city population and country name. 6) List the cities that have the letters “re” in any part of the name; 7) List the tenant name contract number, rent price, when the contract rent price is bigger equal 500 and lower equal 1000. 8) List all columns from apartments with the services offered (all columns from service). 9) List the building address, the city name and the country name for the building in Canada. 10) List the name of the user with the city name and country name, ordering by name of user.
Add the provided information to a new record in the REP table. SELECT * FROM REP Expected Results REP_ID FIRST_NAME LAST_NAME ADDRESS CITY STATE POSTAL CELL_PHONE COMMISSION RATE 35 Fred Kiser 427 Billings Dr. Cody WY 82414 307-555-6309 0.00 0.05
Create formulas that provide this information as follows: In cell H8, create a formula using the DCOUNT function to count the number of projects with funding amounts less than $5,000, using the Lookup table (range A1:E23) as the database, "Funding" as the field, and the range G6:G7 as the criteria. In cell H13, create a formula using the DAVERAGE function to average the funding amounts for Commercial projects, using the Lookup table (range A1:E23) as the database, "Funding" as the field, and the range G11:G12 as the criteria.
Create a procedure to allow company employee to add new product to the database. This procedure needs only IN parameters.
Task 6: The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE_TYPES table to include a new column named MAINTENANCE_PRICE of type FLOAT. ANSWER IN MYSQL PLEASE
In the Testa EV database described in the previous question, fill in the blanks in the following MySQL statement to list (in this order) all the part numbers of components of 'EV Dashboard' (with Part_No=1) and their quantities; sort the results in ascending order of quantity of components (i.e., components with small quantities are listed first). (Please ensure correctness in the spelling of all names and commands. Do not enter unnecessary symbols such as spaces or commas)
List the names of all Staff members who hold a faculty position and have a salary of $45,000 or less. Show their names as their first name, followed by a space, and then their last name but sort the list alphabetically by last name and then first name. Hint: Check out the Position column in the Staff table. All staff members who do not hold a faculty position will get a bonus this year equal to 5% of their salary. List the names, current salary, and bonus amount for each Staff member (non-faculty) who is due a bonus. Show your list in order with the person receiving the highest bonus first.
Using the Categories, Subjects, and Classes tables, list the category description, SubjectID, SubjectCode, and SubjectName for all subject that are taught on Wednesdays. Do not list any subject more than once.
How many times has each language been used in films? Return the value for all six languages. Display: language name and the number of films
4. In the OrderItems table, display a count of the number of products, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BNBG01 BNBG02 BNBG03 BR03 4 rows returned in 0.00 seconds PROD_ID Download 3 3 3 4 NUM_PROD_ID
In anticipation of a change to the database, you are asked to provide a list of all products that have 'Man-Made' or 'Man Made' in the the composition text. (HInt: It is easiest to use LIKE in this instance.) In addition, you are requested to replace either 'Man-Made' or 'Man Made' with the word 'Synthetic'. Only include ProductIDs between 1050 and 1060. Show the following in your answer: ProductID, ProductName, Composition, and the modified composition field as 'NewComposition'. (HINT: This problem is a little tricky because you need to replace both 'Man-Made' and 'Man Made'. The way to do this is to use a replace within a replace.)
The database should contain the following six tables: Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real) Record (artist_id: char(30), track_id: char(30))Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30), num_track_listened: int(10))Listen (user_id: char(30), track_id: char(30))Follow (user_id: char(30), artist_id: char(30), follow_date: datetime) Write a single SQL query for each request below. Find the ids of those users who have listened to at least one track but have not followed any artists. Find the ids and names of those users who have listened to some tracks recorded by the artist named 'Adele'. Find the ids and names of those users who have not listened to any track recorded by the artist named 'Adele'. Use ''NOT EXISTS'' to answer this query.