Question

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 (SCADDR) and phone \((\mathrm{SCPHONE}),\) permanent address \((\mathrm{SPADDR})\) and phone \((\mathrm{SPPHONE}),\) birth date \((\mathrm{BDATE})\) \(\operatorname{sex}(\operatorname{sex}), \text { class (cLass) (freshman, sophomore, } \ldots, \text { graduate }),\) major depart ment (MAJORCODE), minor department (MINORCODE) (if any), and degree program \(\left(p_{R O C}\right)(B, A,, B, S, \ldots, P H, D,) .\) Both sss \(N\) and student number have unique val. ues for each student. b. Each department is described by a name (DNAME), department code (DCOOE), office number (DOFFICE), office phone (DPHONE), and college (DCOLLECE). 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 depart. ment (coept). The course number is unique for each course. d. Each section has an instructor (INAME), semester (SEMESTER), year (YEAR), course (seccourse), and section number (secwum). The section number distinguishes different sections of the same course that are taught during the same semester/ year; its values are \(1,2,3, \ldots,\) up to the total number of sections taught during each semester. e. \(A\) grade record refers to a student \((\operatorname{ss} N),\) a particular section, and a grade \((\mathrm{CRADE})\) 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 \(3 \mathrm{NF}\) or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to render the specification complete.

Short answer

The database schema is designed by first isolating the entities and their respective attributes, identifying functional dependencies and normalizing the relations to 3NF or BCNF. Assumptions were made for unspecified requirements. The primary keys of each relation are also identified, which are unique for each entity.

Step-by-step solution

Identifying the Entities

The entities in this case would be: Student, Department, Course, Section, and Grade record. Each entity should represent an object that is essential in our database and has certain properties (attributes) that we want to record.

Identifying the Attributes

The attributes of each entity would be as follows: \n- Student: SNUM, SNAME, SSN, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, sex, class, MAJORCODE, MINORCODE, PROC. \n- Department: DNAME, DCOOE, DOFFICE, DPHONE, DCOLLECE. \n- Course: CNUM, CNAME, CDESC, cREDIT, LEVEL, coept. \n- Section: secnum, INAME, SEMESTER, YEAR, seccourse. \n- Grade record: SSN, secnum, GRADE.

Identifying Functional Dependencies

Functional Dependencies would include: \n- Student: SNUM -> SSN, SNAME, SCADDR, SCPHONE, SPADDR, SPPHONE, sex, class, MAJORCODE, MINORCODE, PROC. \n- Department: DCOOE -> DNAME, DOFFICE, DPHONE, DCOLLECE. \n- Course: CNUM -> CNAME, CDESC, cREDIT, LEVEL, coept. \n- Section: secnum -> INAME, SEMESTER, YEAR, seccourse. \n- Grade record: SSN, secnum -> GRADE.

Normalization

The relations will be normalized to either 3NF or BCNF by ensuring that each entity is in 1NF (each column contains only indivisible values), is in 2NF (no non-prime attribute in the relation schema is functionally dependent on a part of any of its candidate keys) and is in 3NF (all determinants are candidate keys) or BCNF (every nontrivial functional dependency's determinant is a superkey). The primary keys would be SSN and SNUM for Students, DCOOE for Department, CNUM for Course, secnum for Section, and combination of SSN and secnum for Grade record.

Assumptions

Since it is not mentioned, for simplicity, we assume that the college attribute (DCOLLECE) for the department entity pertains to the university's various colleges rather than referring to other universities.

Key concepts

Functional Dependencies

Functional dependencies play a crucial role in designing a database schema. They establish a relationship between one attribute and another within a database table. Each functional dependency signifies that a particular piece of information determines another. For instance, in the university database, knowing a student's number (SNUM) allows us to determine all other personal details for that student, such as their social security number (SSN), name (SNAME), and other attributes.

This concept is essential because it helps to maintain data integrity and reduce redundancy by preventing duplications in storage. To define functional dependencies, identify the determinant (the attribute on one side of the dependency) and the dependent attributes (the attributes influenced by the determinant). For example, in the `Student` entity, SNUM determines all other student attributes like SSN and SNAME. This tells us that each student has a unique student number, and all their data should be linked to this number in the database. This leads us to effective schema design with minimal redundancy and maximum clarity.

Normalization

Normalization is a process used in relational database design to minimize redundancy and dependency by organizing data efficiently. This process involves dividing a database into two or more tables and defining relationships between them. The goals of normalization are to avoid potential anomalies in data insertions, deletions, and updates.

To achieve normalization, a database schema is typically structured in a series of normal forms. The most commonly used are First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF), along with the Boyce-Codd Normal Form (BCNF).
- **1NF** ensures that each table cell holds a single, indivisible value.
- **2NF** removes partial dependencies from attributes that do not form part of a primary key.
- **3NF** ensures all data dependencies are only on primary keys and removes any transitive dependencies.

For the university database, each table, or relation, needs to maintain these normal forms. This includes organizing data about students, departments, courses, sections, and grade records in such a way that each table has minimal redundancy, and dependencies are clearly defined by primary keys.

Relational Database

A relational database is a type of database that organizes data into tables, or relations, which can be linked—or related—based on data common to each. Each table typically contains one type of entity and details attributes relevant to that entity. In a university setting, tables might include data related to students, courses, or grade records.

The power of a relational database lies in its ability to enforce relationships and constraints based on data integrity rules. Through the use of keys like primary keys and foreign keys, data can be accurately cross-referenced. For instance, the `SNUM` in the `Student` table acts as a unique identifier connecting various elements of student data with records in related tables, like the `Grade record` table.

Relational databases support complex queries, allowing users to perform sophisticated data analysis and manipulation. This model emphasizes both data integrity and the relational ties between different datasets, making it an ideal choice for handling large amounts of consistent data, such as in a university database context.