Database Modeling and ER Diagram - Essay Example

Database Modeling and ER Diagram: Normalization of a database is carried out in a series of steps. In this case, we will consider the first normal form, second normal form and third normal form. Each form has its own set of conditions that need to be met for the database to be considered fully normalized (Maslakowski & Tony, 2000). This database contains the following tables: Student-information and Course-information. The student-information table contains the following fields: Student id, name, phone, email, street number, street name, city, and state.

This table does not satisfy the requirements of 1NF since it contains multi-valued fields. In this table, possession of more than one phone number or email address is not allowed. It might be important to have alternative contact addresses for students in case of unexpected situations. Therefore, in order to cater for such a situation, we need to create additional tables to handle the addresses. One table named email-address will contain the following fields: id, email address, and student-id.

Likewise, another table named phone-number will be created containing the following fields: id, phone number, and student-id. Finally, another table will be street details. This table will have the following fields: id, street name, city name, state, and student id. Figure 1: ER diagram showing student-information table after normalizationFunctionStatementDrop student-information table drop TABLE Trident.student-information Create table student-informationCREATE TABLE Trident.student-information( student-Id INT NOT NULL, Name VARCHAR(45) NOT NULL, PRIMARY KEY(student-Id));Create table email-addressCREATE TABLE Trident.

email-address( id INT NOT NULL AUTO_INCREMENT, email-address VARCHAR(45) NOT NULL, student-Id INT NOT NULL, PRIMARY KEY(id));Create a relationship between email-address and student-information tables.ALTER TABLE Trident.email-addressADD FOREIGN KEY (student-Id) REFERENCES Trident. student-information (student-Id); Create table phone-numberCREATE TABLE Trident. phone-number( id INT NOT NULL AUTO_INCREMENT, phone-number VARCHAR(45) NOT NULL, student-Id INT NOT NULL, PRIMARY KEY(id)); Create a relationship between phone-numbers and student-information tables.

ALTER TABLE Trident.phone-numberADD FOREIGN KEY (student-Id) REFERENCES Trident. student-information (student-Id); Create table street-detailsCREATE TABLE Trident. street-details( id INT NOT NULL AUTO_INCREMENT, street-name VARCHAR(45) NOT NULL, city-name VARCHAR(45) NOT NULL, state VARCHAR(45) NOT NULL, student-Id INT NOT NULL, PRIMARY KEY(id)); Create a relationship between street-details and student-information tables.ALTER TABLE Trident.street-detailsADD FOREIGN KEY (student-Id) REFERENCES Trident.

student-information (student-Id); Table 1: Creation of new tables and their relationshipsThe four tables created satisfy the requirements of 1NF. They are also in 2NF since they do not contain any partial dependencies. All fields in each table are uniquely reliant on the respective primary key as 2NF requires (Buxton, 2009). The four tables also fulfill the requirements of 3Nf since all fields present depend on the primary key for their values. The next table that requires normalization is named course information.

This table contains the following fields: Course-Id, course title, and course credit. This table is already in its first normal form since it does not contain multi vamulti-valuedlued fields. This table also fulfills the requirements of 2NF since all fields in the table rely uniquely on the primary key. This table also fulfills the requirements of 3NF since each field in that table is dependent on the primary key. The last thing now is to create a relationship between a student and the courses that they take. This can be done by employing a many-to-many relationship between the student-information table and the course-information table.

In order to accomplish this relationship, another table is required. This table will contain the primary keys of both tables and an additional field named student credit. In this way, a student can take many courses and many students can take one course. Figure 2: Final ER diagramReferenceBuxton, S. (2009). Database Design: Know It All. Amsterdam: Morgan Kaufmann/Elsevier.Malinowski, M., & Tony B. (2000). Sams Teach Yourself MySQL in 21 Days. Indianapolis, IN: Sams.