Question

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), cours 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.

Using your DBMS, build this database and populate it with several entities. (Use your imagination)

Using your DBMS, build this database and populate it with several entities. (Use your imagination)

Using your DBMS, build this database and populate it with several entities. (Use your imagination)

Please help me with the set up SQL. thank you

Answer 1

Fuctional dependency is defined as a relationship between the two attributes of same table. FD helps in preventing anomalies and data redundancy.

These are Functional dependencies that can be inferred from given scenario:

Explanation for Functional dependencies

Primary key assumptions:

Primary key of STUDENT - SNUM

Primary key of DEPARTMENT - DEPTCODE

Foreign key assumptions:

STUDENT.MAJOR -> DEPARTMENT.DEPTCODE

STUDENT.MINOR -> DEPARTMENT.DEPTCODE

COURSE.CDEPT -> DEPARTMENT.DEPTCODE

SECTION.SECCOURSE -> COURSE.CNUM

GRADES.(SECCOURSE, SEMESTER, YEAR, SECNUM) -> SECTION.(SECCOURSE, SEMESTER, YEAR, SECNUM)

GRADES.SNUM -> STUDENT.SNUM

Database creation steps:

/* Create a table called student */
CREATE TABLE student(snum int PRIMARY KEY, ssn int unique, sc_addr varchar(50), sc_phone int, sp_addr varchar(50), spp_phone int, b_date int, sex char(10), class char(50), prog char(50), major_code char(20), minor_code char(20));

/* Create a table called department */
CREATE TABLE department(dcode int primary key, Dname char(20) unique, Doffice int, Dphone int, Dcollege char(50));

/* Create a table called course */
CREATE TABLE course ( Cnum int primary key, Cname char(20) unique , Cdesc char(100), credit int, level int, Cdept char(20));

/* Create a table called section */
CREATE TABLE section(sec_course char(20), sec_num int, semester int, year int, instructor_name char(20) );

/* Create a table called grades */
CREATE TABLE grades(sec_course char(20), sec_num int, semester int, year int, grade char(5));

/* Create few records in these table */

INSERT INTO student VALUES('Tom', 1, '101 park avenue', 12345, '102 street',1277, 20-02-1998, 'M', 'Global', 'C++', 'CS01', 'A');

INSERT INTO department VALUES( 101, 'Computer', 18001, 7869, 'Sunshine coast');

INSERT INTO course VALUES( 11, 'management', 'deals with management topics', '50 hours', 1, 'Engineering');

INSERT INTO section VALUES( 'Programming', 2, 4, 2001, 'Harry');

INSERT INTO grades VALUES('Programming', 2, 4, 2001, 'Harry', 'C');