Question

14.19. Suppose that we have the following requirements for a university database that is used to...

14.19. Suppose that we have the following requirements for a university database that is used to keep track of students’ transcripts:

a. The university keeps track of each student’s name (Sname), student number (Snum), Social Security number (Ssn), current address (Sc_addr) and phone (Sc_phone), permanent address (Sp_addr) and phone (Sp_phone), birth date (Bdate), sex (Sex), class (Class) (‘freshman’, ‘sophomore’, … , ‘graduate’), major department (Major_code), minor department (Minor_code) (if any), and degree program (Prog) (‘b.a.’, ‘b.s.’, … , ‘ph.d.’). Both Ssn and student number have unique values for each student.

b. Each department is described by a name (Dname), department code (Dcode), office number (Doffice), office phone (Dphone), and college (Dcollege). Both name and code have unique values for each department.

c. Each course has a course name (Cname), description (Cdesc), course number (Cnum), number of semester hours (Credit), level (Level), and offering department (Cdept). The course number is unique for each course.

d. Each section has an instructor (Iname), semester (Semester), year (Year), course (Sec_course), and section number (Sec_num). 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 total number of sections taught during each semester. e. A grade record refers to a student (Ssn), a particular section, and a grade (Grade). Design a relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to render the specification complete.

Homework Answers

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Consider the following set of requirements for a UNIVERSITY database that is used to keep track...
Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students’ transcripts. This is similar but not identical to the database shown in Figure 1.2: a. The university keeps track of each student’s name, student number, Social Security number, current address and phone number, permanent address and phone number, 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...
SQL Data: you can just copy paste it into mysql to. CREATE DATABASE University;USE University; CREATE...
SQL Data: you can just copy paste it into mysql to. CREATE DATABASE University;USE University; CREATE TABLE Student (  sid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  address VARCHAR(20) NOT NULL,  major CHAR(2)); CREATE TABLE Professor (  pid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  department VARCHAR(10) NOT NULL); CREATE TABLE Course (  cid INT PRIMARY KEY,  title VARCHAR(20) NOT NULL UNIQUE,  credits INT NOT NULL,  area VARCHAR(5) NOT NULL); CREATE TABLE Transcript (  sid INT,  cid INT,  pid INT,  semester VARCHAR(9),  year YEAR,  grade CHAR(1) NOT NULL,  PRIMARY KEY (sid, cid, semester, year),  FOREIGN KEY (sid) REFERENCES Student...
Draw the scenario using Chen notation In a university, department offer courses. Department has instructors who...
Draw the scenario using Chen notation In a university, department offer courses. Department has instructors who teach courses. For a course, the instructor may or may not have a teaching assistant. Each department has a department name, a department code, 1-4 phone numbers, and an address. ◦ Each course has a course number (e.g., INFO605), a section number (e.g., 900), a title, a course type (e.g., online), and units. Each instructor has an instructor ID, first name, last name, gender,...
3-students take courses. Someone design this table: Student_Course (student name, ID , SSN , course number,...
3-students take courses. Someone design this table: Student_Course (student name, ID , SSN , course number, grade, home address, course credit ) Description: ID, SSN; each one has a unique value for a given student. Given ID and course number we can get grade. Given SSN and course number we can get grade. Given ID we can get everything except grade and course credit. Given SSN we can get everything except grade and course credit. Given course number we can...
Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber,...
Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00PM. Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade. I'm looking for the DLL statements to make the tables above in SQL
A university is made up of students and professors.  Students enroll in classes.  Professors teach classes.  Each student has...
A university is made up of students and professors.  Students enroll in classes.  Professors teach classes.  Each student has a unique student ID number, a name, phone number, and a major.  Each professor has a unique employee ID number, a name, phone number, and department name.  Draw an ER (Entity-Relationship) Diagram for this scenario.  Include the proper symbols and connecting lines for all entities, attributes, and relationships.
A university database contains the following information about each student – their full name, their student...
A university database contains the following information about each student – their full name, their student ID number (a randomly assigned 8-digit number), their date of birthday, their sex, their age and the year they joined the university. Identify which data-type each attribute belongs to, and explain your answer.
UC State University is developing a homework database to track assignments, classes, professors, and departments (History,...
UC State University is developing a homework database to track assignments, classes, professors, and departments (History, English, etc). Each professor, the homework database must produce their first name, middle initial, last name, title (Mrs,etc), email address, and telephone number. Each course has one professor and the homework database should save a meeting time, room number, description, and name. Each assignment has assigned date, due date, and assignment description. This homework app must also remember which course has which assignment and...
Consider the following relational database:               STUDENT     (STDNO, SNAME, DEPTNO#)             &n
Consider the following relational database:               STUDENT     (STDNO, SNAME, DEPTNO#)               DEP                 (DEPTNO, DNAME)               COURSE        (CORSNO, CNAME, DEPTNO#, CMAX)               ENROLMENT       (STDNO#, CORSNO#, GRADE, EDATE) STUDENT.DEPTNO is the department number of the student. COURSE.DEPTNO is the department number that offers the course. ENROLMENT models the registrations of students in courses (M:M relationship) where the GRADE is the grade obtained by the student in the course. PART I : DATA DESCRIPTION LANGUAGE (DDL) Write DDL commands to create the four...
Design an E-R diagram to model a college described below: ● There are several departments in...
Design an E-R diagram to model a college described below: ● There are several departments in a college, and each of them has a unique name, one or more telephone numbers, and an address. ● Each department gives some number of courses. Each course has a course number which can be used to identify a course only inside of the same department. Each course has its schedule and a certain number of credits. Each course is held in some location...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT