Data Modeling and Poor Structured Data Models in Databases - Assignment Example

Running Head: Data Modeling Question 1 a) Entities and primary key The following are entities listed in a table; UML table showing job history entities candidate number as the primary key Job history table is where the workers details are recorded once hired for a job by the CEPA it is used when they are looking candidate to hire. Candidate number is used as primary key in this table the primary key here identifies each candidate uniquely. Company request table: this is a table with details about a given company that requests for temporary workers from the CEPA. The opening number is used as a primary key which identify each company request uniquely. UML table showing company request entities opening number as primary key Candidates Table; this is where the candidate’s qualifications are recorded, major or specific qualifications are recorded, candidate number is used as a primary key. UML table for candidate’s details candidate number as the primary key b) Cardinality and participation constraints 1. The relationship between company that requests workers and candidates available for the work is that one or many candidate(s) is qualifies to be hired temporarily by one or many companies looking for workers it implies that there can be one or many candidates whose qualifications can match the requirements of many or one company . One or many candidates can be hired by one company can hire , a company can have job requirements for one or many candidates but only one company can hire the candidate a candidate can be hired y many companies at the same time. one or many candidate(s) is (are) qualifies to be hired temporarily by one or many companies One or many candidates can be hired by one company. 2. The relationship between job history and the candidate’s qualification is one to one relationship in both cases where one candidate can have a job history and only one history belongs to a single candidate; there cannot be a one job history for many workers. 3. The relationship between the job history and the company is the same as that of the candidate and company because job history keeps records of workers who are candidates to be hired. c) many to many relation Many to many relationship exists between company that requests workers and candidates available for the work whereby one or many candidate(s) is qualifies to be hired temporarily by one or many companies looking for workers it implies that there can be one or many candidates whose qualifications can match the requirements of many or one company . d) ERD Question 2 1. Update anomalies this is an effect that results from poor structured data models in databases which brings about repeated data know as redundant data. Update anomalies can be are either insert anomalies; update anomalies and delete anomalies. In the above table there is repeated data and the following are the anomalies; Insert anomalies The problem in the table is that there is a lot repeated information (subCode, staffName, Address, staffNO, classDay, classTime, YearSem, classroom, Credit subName). It is not possible to identify primary key because there is repetition with staffNO with multiple rows. It is not possible to add other lecturer details in the timetable because of the poor design of the table. Update anomalies Repeated data groups such as that of lecturer and room related. The anomaly will happen if we try to update data related to lecture, subcode and class rooms in the timetable for instance if a subName of the course is changed to for IRD to COS a single entry changes will not be effected to all rows, an update will be required for each row. Delete anomalies Deleting a group of data like staff member and course it will require that all the data associated with staff or course to be deleted. One can lose data that is very important and which did not indent to delete. 2. Candidate keys A candidate key is an entity that can be used as a primary key as either combined or a single entity. The following are candidate key a. subCode- b. staffNO 3. staffNO as primary key Given that this is lecturer’s timetable I will use staffNO as a primary key from the candidate keys. Lecturers will be identified uniquely by the numbers. To analyze the dependency From the figure subcode does not rely on the staffNO the code can exist without the existence of the staff therefore there is no dependency. staffName and address relies on the staffNO, the two entities cannot exist without the staff number. These columns cannot exist without the staffNO. classTime does not rely on the staffNo there can be a time for the class without having a staff. yearSem does not rely on the staff there can be a year of the semester without having a staff member. classroom is an entity that can exist without having a staff member hence it does not rely on the staffNO. The entity credit does not rely o the staffNo, it implies that credit can exist without StaffNO. The subName entity does not rely on staffNO. From the analysis it can be concluded that having staffNO as a primary key has only two dependencies these are entities that cannot exist on their own without staffNO while the rest of the entities do not depend on the primary key selected. The analysis is demonstrated in the dependency diagram below. Dependency diagram ER diagram showing two tables after dependency analysis using staffNO as primary key After the dependency analysis there can be two tables one with the staffNO as primary key I have named this table as staffmember it has details of the staff member who is the lecturer, there is another table which I have retained its name with other columns that do not dependent on staffNO. StaffNO is used in the second table to be referenced when entering values about that particular staff. CK stands for candidate key. Normalization to third form The following tables are created after 3NF Lecturer(staffNO, subCode ,staffName, Address) Subject(subCode, subName,credit,yearSem,staffNO) Subject Lecturer(staffNO, subCode,classRoom classDay,classTime,) . ERD showing 3NF of the lecturer timetable The following relationships can be found in the tables; The relationship between the subject lecture and course One course (subject) can be associated with many lecturers but at least one. Each course is associated with many subject lecturers and only one. The relationship between the lecturer and the course Zero or Many lecturers can be associated with the course and one or many courses can be assonated lecturers. A course is associated with one or more lecturers but at least one, one or more lectures is associated with a course BCNF Having candidates keys cannot be dealth with satisfactorly in 3NF where there are composite candidate keys having one or more common attributes. The concept of detemintant is used in BCNF where a determinant is any attribute which other attributes totally functionaly dependent. To be satisfied that a relation in 3NF is in BCNF is if only every determinant is a candidate key. The following are the determinants where in BCNF every determinant is a candidate key Lecturer(staffNO, subCode ,staffName, Address) Subject(subCode, subName,credit ,staffNO) Subject Lecturer(staffNO, subCode,classRoom classDay,classTime,) The bold and underlined attributes are the candidate keys Lecturer: only determinant is staffNO Subject: only determinant is subCode Subject Lecturer: the determinants are staffNO, subCode candidate keys staffNO and subCode The table is in BCNF because if we delete information about a given subject we arestill able to know who was the subject lecturer. We can also know which subjects a lecturer can lecturer even if we do not have students registered for that particular subject. Therefore the 3NF is in BCNF. Read More