logo

Relational Model | Purpose Of Normalization | Assignment

6 Pages1075 Words249 Views
   

Added on  2019-09-26

Relational Model | Purpose Of Normalization | Assignment

   Added on 2019-09-26

ShareRelated Documents
Q (a)RELATIONAL MODELPredicate schema is:Student (number, name, degree)Course (code, cname)Term (name, student_number, Course_code)Taking (number, code, term)The relational schema is:STUDENTNUMBERNAMEDEGREECOURSECODECNAMETERMName STUDENT_NUMBERCOURSE_CODETAKINGNUMBERCODETERM
Relational Model | Purpose Of Normalization | Assignment_1
The purpose of the normalization is to reduce redundancy and prevent update anomalies. In the above diagram we can see that through the ER diagram, 3 tables have been designed in the relational model. In the first table Student where “number”is a primary key to identify each record uniquely. In the second table Course where code is the primary key to identify each record. And the third table ‘term’Taking has been designed to model the ‘taking’ relation between the tables. The ‘term’‘taking’ table has term namestudent number and course code as primary key to identify which student has which course in each term. This method is best has been done bynormalizing the whole relation between student and the course. Because there will not be any redundancy in those table since student data, course data and relationship between student and course are being stored in separated table.Q (b)INTEGRITY CONSTRAINTS The integrity constraints are:1.Each Student will have a unique student ‘number’ which will be a primary key.This constraint is necessary so that each student record can be identified directly by the number and can be stored uniquely.2.Each Course will have a unique course ‘code’ which will be a primary key. This constraint is necessary so that each course info can be identified directly by the code and can be stored uniquely3.Each Student will havetake at least one course and may take up to four courses. This constraint is necessary to maintain the foreign key relationship cardinality constraint between “Stundent” and “Taking” relationship
Relational Model | Purpose Of Normalization | Assignment_2
4.There will terms for each of the 4 courses: this is necessary to maintain the (1,4) relationship between the course and termEach Course should have at least one or more students to maintain the cardinality constraint between “Course” and “Taking” relationship.5.The term name, course code and student number together will be taken together which will be a primary key to identify each record. The Taking table unique combination of course code and student number which will be the primary key of the table.6.Each course term will have one or more students in it. All these will help in eliminating redundancy, update anomalies, insert or delete anomalies and will make the relational database more consistent and reliable. Student number which appears in the Taking table must be in the Student table inorder to satisfy referential constraint.7.Course code which appears in the Taking table must be in the Course table inorder to satisfy referential constraint.Q (c)PREDICATE LOGIC OF SCHEMAStudent entityStudentDC Dom (Number) x Dom (Name) x Dom (Degree)This predicate logic shows that Student is made up of 3 finite attributes. These attributes are subset of the relation student. Course entityCourseDC Dom (Code) x Dom (Cname)
Relational Model | Purpose Of Normalization | Assignment_3

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Job1 Assignment Solution - Relational Model, Integrity Constraints, Queries, SQL, Views, Active Rules
|6
|778
|332

Answers To Question Number - 1
|9
|1687
|27

Assignment on DBMS Architecture
|6
|1843
|16

Course Title and Code:.
|5
|645
|1

Collection of Logically Related Data - Desklib
|6
|563
|99

Database Systems
|7
|609
|235