Consider the following set of requirements for a
university database that is used to keep track of students'
transcripts
The university keeps track of each student's name,
student number, social security number, address and phone,
birthdate, gender, `, and degree program (bachelor, Masters, PhD.).
Both social security number and student number have unique values
for each student.
Each department is described by a name, department
code, office number, office phone. Both name and code have unique
values for each department.
Each course has a course name, description, and course
number, number of semester hours, level, and offering department.
The value of course number is unique for each course.
Each section has an instructor, semester, year,
course, and section number. The section number distinguishes
different sections of the same course that are taught during the
same semester/year; its values are 1, 2, 3, ...; up to the number
of sections taught during each semester.
A grade report has a student, course, section, letter
grade, and numeric grade (0, 1, 2, 3, 4 for F, D, C, B, A,
respectively).
Design an ER schema for this application, and draw an ER diagram for that schema.
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.
The ER diagram required is given below.
Entity : Attributes (Bold are Primary key)
STUDENT : SNum , SSN , Birthdate, Gender, Class, Sname(First Name, Last Name, Middle Name), Caddress(Street, City, State, ZIP), Padd, CPhone, Degree Program.
DEPARTMENT : Dname , Doffice, Dcode, DPhone, College.
COURSE : Cnum, Cdescription, Cname, Semester Hours, Level
SECTION : Partial Key(SNum, Semester, Year) Instructor.
GRADE : Numeric Grade, Letter Grade
STUD_SECT = Student Section
MINOR_DEPT = Minor Department
MAJOR_DEPT = Major Department
DEPT_COURSE = Department Course
COURSE_SECT = Course Section
Consider the following set of requirements for a university database that is used to keep track...
Problem 1 Consider the following set of requirements for a university database that is used to keep track of students' transcripts. (a) The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, -, graduate), major department, minor department (if any), and degree program (B.A., B.S., ...., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address,...
Question 1: University Database Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts. The university keeps track of each student's name, student number, social security number current address and phone, permanent address and phone, birth date, sex, class (freshman, sophomore,., graduate), major department, minor department (if any), and degree program (B.A., B.S.,..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address,...
Conceptual data diagram The ABC university keeps track of each student's name, student number, social security number (I C number), current address and phone, permanent address and phone, birthdate, sex, class (alpha, Beta, Gamma ..., graduate), major department, minor department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address, and to the student's last name. Both social security number and student number have...
Please build an Entity Relationship Diagram using the following information: Build a database for a regional university in Western New York called Ithica South University. The university database administrator needs to keep track of the students that attend the university, the courses offered by the university, and the university employees. Ithica tracks the first and last name of each student, as well as their target degree. The university keeps track separately of two subclasses of students, as students are either...
10- Specify the following queries in SQL on the database schema of Figure 2. a. Retrieve the names of all senior students majoring in ‘CS’ (Computer Science). b. Retrieve the names of all courses taught by Professor King in 2004 and 2005. c. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section. d. Retrieve the name and transcript of each senior student (Class =4) majoring in CS. A...
Question: Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course. 1- Draw an ER diagram of ONLINE COURSE REGISRATION database, that captures all the given below requirements. Specify key attribute(s) of each entity set. For each relationship set, specify structural constraints and participation constraints. a. For each USER, the portal maintains user ID, Name, E-mail. Each user has a unique ID. Name is a Composite attributes with Frame, Midname,...
Develop a conceptual ER diagram for the above database description, using appropriate syntaxes, and showing cardinalities and key attributes. Consider the partial description for a UNIVERSITY database below: A university is organized into colleges, and each college has a unique name, a main office and phone, and a particular faculty member who is dean of the college. Each college administers a number of academic departments. Each department has a unique name, a unique code number, a main office and phone,...
Deck Scudder, the chairperson of the information technology department at the University of Denver, needs to create a database to keep track of all the courses offered by the department. In addition, Deck would like the database to include each instructor’s basic contact information, such as ID number, name, office location, and phone number. Currently, Deck has nine instructors (seven full-time faculty members and two adjuncts) in the department. For each course, Deck would like to keep track of the...
Consider the Schema diagram for a university database a. Find the IDs of all students who have taken all of the CMPSC courses. Use the division operation. b. Do the same operation as in problem a, using only fundamental operations. student takes ID course id sec id semester 1er 4112 dept_name tot cred grade advisor section course_id sec id semester year building TOOM_10 time_slof_id course course id title dept name credits department dept name building budget time slot time slot...
Create sql queries to find the following miscrosoft access compatible commands please 9. Courses for which at least three students earned a grade of A, showing the grade and the course code 10. Semesters when the highest number of courses have been scheduled, showing the semester name and the number of sections 11. (Extra) Professors that are not students, and students that are not professors, showing their ID and SSN 12. (Extra) Semesters for which all courses are taught by...