Database Design for Professors: 3NF and ERD Implementation

Verified

Added on  2022/11/14

|5
|450
|63
Project
AI Summary
This document presents a comprehensive solution for a professor database design project. It begins with a dependency diagram illustrating the relationships between various attributes such as employee number, professor rank, employee name, department code, department name, professor office, advisee, committee code, and journal code. The solution identifies multi-valued dependencies and proceeds to normalize the data to the third normal form (3NF). The process involves breaking down the initial table into multiple tables to eliminate redundancy and ensure data integrity. The document outlines the table structures in 1NF, 2NF, and 3NF. The final 3NF structure includes separate tables for professors, departments, advisees, committees, and journals, with appropriate primary and foreign keys to maintain relationships. A Crow's Foot Entity-Relationship Diagram (ERD) visually represents the database schema, illustrating the entities and their relationships. Finally, the document provides a list of relevant references. This solution is designed to assist students with their database design assignments, providing a clear and structured approach to relational database modeling and normalization.
Document Page
1Professor Database Design
PROFFESOR DATABASE DESIGN
[Student name]
[University name]
[Professor Name]
[Date]
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
2Professor Database Design
1 Dependency Diagram
(Thomas, 2013)
2 Identified Multi-valued Dependencies
Below are the multi valued dependencies from the above dependency diagram where some
attributes are related in multiple forms with the other attributes in the non normalized professor
table.
a) EMP_NUM ----------> ADVISEE, COMMITEE_CODE, JOURNAL_CODE (Shamkant,
2015).
EMP_NUM PROF_RANK EMP_NAME DEPT_CODE DEPT_NAME PROF_OFFICE ADVISEE COMMITEE_CODE JOURNAL_CODE
The above three are multi-valued
Multi-valued(ADVISEE, COMMITEE_CODE, JOURNAL_CODE) (Ramez, 2015).
EMP_NUM PROF_RANK EMP_NAME DEPT_CODE DEPT_NAME PROF_OFFICE ADVISEE COMMITEE_CODE JOURNAL_CODE
ADVISEE COMMITEE_COD
E
JOURNAL_CODE
Document Page
3Professor Database Design
3 Dependency Diagrams Tables Structure In 3NF
1 NF
EMP_NUM PROF_RANK EMP_NAME DEPT_CODE DEPT_NAME PROF_OFFICE ADVISEE COMMITEE_CODE JOURNAL_CODE
T.D
1NF(EMP_NUM,PROF_RANK,EMP_NAME,PROF_OFFICE,ADVISEE,COMMITEE_CO
DE,JOURNAL_CODE) (Elmasri, 2013).
Transitive dependency (DEPT_CODE, DEPT_NAME)
2NF
EMP_NUM PROF_RANK EMP_NAME DEPT_CODE DEPT_NAME PROF_OFFICE ADVISEE COMMITEE_CODE JOURNAL_CODE
T.D
2NF(EMP_NUM,PROF_RANK,DEPT_CODE,DEPT_NAME,EMP_NAME,PROF_OFFICE,
ADVISEE,COMMITEE_CODE,JOURNAL_CODE)
Transitive dependency (DEPT_CODE, DEPT_NAME)
3NF
PROFFESOR
EMP_NUM PROF_RANK EMP_NAME PROF_OFFICE ADVISEE COMMITEE_CODE JOURNAL_CODE
DEPARTMENT
3 NF(EMP_NUM, PROF_RANK, EMP_NAME, PROF_OFFICE, ADVISEE, COMMITEE_CODE, JOURNAL_CODE)
3 NF(DEPT_CODE, DEPT_NAME) (Stephen, 2014).
DEPT_CODE DEPT_NAME
Document Page
4Professor Database Design
PROFFESOR
PKEMP_NUM
PROF_RANK
EMP_NAME
PROF_OFFICE
TELPHONE
ADDRESS
FKDEPT_CODE
FKADV_CODE
FKCOMMITEE_CODE
FKJOURNAL_CODE
DEPARTMENT
PKDEPT_CODE
DEPT_NAME
LOCATION
ADVISEE
PKADV_CODE
ADV_NAME
TELPHONE
ADDRESS
COMMITEE
PKCOMMITEE_CODE
COMMITEE_NAME
COMMITEE_ROLE
JOURNAL
PKJOURNAL_CODE
JOURNAL_TITTLE
JOURNAL_PUBLISHER
JOURNAL_AUTHOR
JOURNAL_EDITION
4 Crows Foot ERD
(Dick, 2018).
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
5Professor Database Design
5 Reference
Dick,B.(2018) IT Project Management Essentials: Information Systems Project Methodology
Kindle Edition.1st edn.New York:Amazon Digital Services LLC.
Elmasri,E.(2013) Database Systems: Models, Languages, Design and Application Programming.
2nd edn India: PEARSON EDUCATION.
Ramez,E.(2015) Database Systems: Models, Languages, Design and Application Programming
- International Edition Paperback.2nd edn. India:PEARSON EDUCATION.
Shamkant, B.(2015) Fundamentals of Database Systems (7th Edition).India:pearson.
Thomas,C.(2013) Database Systems: A Practical Approach to Design, Implementation and
Management: WITH Success in Your Project.1st edn. USA:Addison Wesley.
Stephen,H.(2014) Management Information Systems with student CD and MISource.3rd edn. New
York:McGraw-Hill.
chevron_up_icon
1 out of 5
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]