ISQA 3310 Managing The Database Environment Assignment 3 Nor

Isqa 3310 Managing The Database Environmentassignment 3 Normalizati

This assignment involves analyzing sample data related to student grade reports, identifying functional dependencies, and performing normalization to achieve third normal form (3NF). Additionally, it includes developing an ER model using ERwin software, evaluating and improving an initial ER model for a parking ticket system, and creating an ER diagram with proper referential integrity constraints. The assignment concludes with a reflection on the experience and challenges faced during these tasks.

Paper For Above instruction

Introduction

Database normalization is a fundamental process in designing efficient and reliable relational databases. It involves organizing data to reduce redundancy and dependency by dividing large tables into smaller, well-structured tables while preserving the relationships among them. This paper addresses the key aspects of normalization, ER modeling, and database design as outlined in the assignment, focusing on a student grade report system and a parking ticket management system. The process involves identifying functional dependencies, decomposing tables to meet 3NF standards, creating ER diagrams, and reflecting on the challenges encountered during these procedures.

Task 1: Analyzing Student Grade Report Data

The first task involves examining the provided student grade report data and identifying the functional dependencies. The data includes attributes such as student ID, name, campus address, major, course ID, course title, instructor name, instructor location, and grade. Based on the data, we can deduce the following functional dependencies:

• st_ID → st_name, campus_address, st_major
• course_ID → course_title, inst_name, inst_location
• inst_name → inst_location
• st_ID, course_ID → grade
• st_major → major (assuming each student has a unique major)
• course_title → course_ID (since each course has a unique title)

From these dependencies, the primary key for the relation is the composite of student ID and course ID, as each student receives one grade per course. The initial relation is converted into 1NF by ensuring atomic attributes. The relation can be represented as:

This version removes repeating groups and ensures each attribute contains atomic values.

Task 2: Decomposing into 3NF

Using the identified functional dependencies, the next step is to decompose the relation into tables that satisfy 3NF. This process helps eliminate transitive dependencies and ensures each table represents a single entity type. The decomposition results in the following tables:

Students

Majors

Courses

Instructors

Grades

Explanation: The 'Students' table contains student-specific info; 'Majors' stores unique majors; 'Courses' and 'Instructors' contain course and instructor info respectively, with courses linked to instructors; 'Grades' links students and courses, recording each student's grade—fulfilling normalization standards.

Task 3: ER Diagram Using ERwin

The ER diagram models the entities and their relationships derived from the normalized tables. Entities include Student, Major, Course, Instructor, and Grade. Relationships reflect:

• Students enroll in Courses (many-to-many, resolved via Grades)
• Courses are taught by Instructors (many-to-one)
• Students have Majors (many-to-one)

The ERwin diagram should implement primary keys, foreign keys, and enforce referential integrity, with relationships annotated with verb phrases, such as "enrolls in" or "is taught by." The diagram should clearly depict the logical schema, including attributes, primary keys, foreign keys, and relationship cardinalities, with integrity constraints properly enforced.

Task 4: Evaluation and Improvement of Parking Ticket ER Model

The initial ER model for parking tickets includes attributes such as student ID, last name, first name, phone number, state license, license number, ticket number, date, code, violation, and fine. Functional dependencies include:

• ticket_no → stID, LName, FName, PhoneNo, State, Lic, LicNo, Date, Code, Violation, Fine
• stID → LName, FName, PhoneNo
• Lic, LicNo → stID
• Code → Violation, Fine

Transitive dependencies are present, such as license details determining the student, suggesting the need for decomposition:

Proposed decomposed entities:

• Students: stID, LName, FName, PhoneNo, Lic, LicNo
• Vehicles: Lic, LicNo, stID
• Tickets: ticket_no, Date, Code, Violation, Fine, stID
• Violations: Code, Violation, Fine

The ER diagram should be drawn in ERwin, indicating primary and foreign keys, relationships, and referential integrity constraints. Proper normalization minimizes redundancy and ensures data integrity.

Conclusion

Implementing normalization and ER modeling facilitates efficient database design by eliminating anomalies and ensuring data consistency. Challenges such as identifying correct dependencies and suitable decomposition require a thorough understanding of the data and dependencies. Utilizing tools like ERwin enhances clarity and enforcement of constraints, making the database scalable and maintainable. Reflection indicates that systematic analysis, iterative refinement, and adherence to normalization principles are key principles applicable across various database design tasks.

References

• Date, C. J. (2012). An Introduction to Database Systems (8th ed.). Pearson.
• . Morgan Kaufmann.
Fundamentals of Database Systems (7th ed.). Pearson. Database Modeling and Design. IBM Redbooks. Database Design: Know It All. Morgan Kaufmann. Database System Concepts (6th ed.). McGraw-Hill. Communications of the ACM, 13(6), 377-387. ACM Ubiquity, 3(4), 2-7. Mining of Massive Datasets. Cambridge University Press.