Figure P1.1: The File Structure for Problems 1-6
1. What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure?
The city names are comprised in the MANAGER_ADDRESS feature and enlisting this character at the application level is a problem. The queries are quite hard to write and take extended period of time to execute when searches are undertaken. This can be solved by storing the city name as a different feature (Wiederhold, 1983).
2. What data redundancies do you detect, and how could these redundancies lead to anomalies?
Taking into consideration that the name ‘Holly B. Parker appears more than once, indicating that she is able to complete three projects identifiable as 21-5Z, 25-9T and 29-2D. This tendency shows us that there is 1: M relationship that is noted between the PROJECT and MANAGER: every project is handled by a single manager though the he is able to manage more than one project. Ms. Parker’s phone number and address similarly is noted to appear three times. Taking a situation where Ms. Parker shifts and/or alters her phone number, these corrections have to done severally and done in the correct way. No feature should be left out (Haithcoat, n.d). A situation where an event is not there in the correction process, the data is not similar for the same individual. After a certain period of time, it may be hard to identify an accurate data. Moreover, several happenings lead to misspellings and errors consequently bringing about the same errors. The same issues are there for more than one appearance of George F. Dorts.
3. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.5.
The figure P1.5 shows several records present for all of the clients. It shows that a client record is there for all of the projects the client is connected to. Information is possible to be maintained in a number of areas. A good example is the client’s phone number for staff labeled 105 who has two entries. The first one is recorded under Hurricane project while the second one is in the Satellite project. In any case there is a change in details; the integrity of the data would be affected since it is improbable that it would be done to the whole project.
4. Looking at the EMP_NAME and EMP_PHONE contents in Figure P1.5, what change(s) would you recommend?
I would go for a table for client details and another table for project information. The EMP_NUM would be used as the primary key for the employee table and as the foreign key in the project table. This can be deduced that the phone number would appear in the employee table and would be kept in the employee record (Ullman, 1982). This would hence do away with redundant data and islets of information.
5. Identify the different data sources in the file you examined in Problem page 30, #6.
The data sources that are found in the file system are personal, payroll and project.
6. Given your answer to Page 30, #7, what new files should you create to help eliminate the data redundancies found in the file shown in Figure P1.5?
The new files that would be created so as to do away with the data redundancies would be one file for every project, which would be connected to the personal and payroll records.
7. Given the file structure shown in Figure P1.9, what problem(s) might you encounter if building KOM were deleted?
The problem that would be noted with the removal of the KOM file would be that the rooms would be left with nothing to look after them. A student will be in a position to get the teacher and room, however they will not know what structure the class is based. The structure and the room ought to be connected on a one to many form of relationship.
8. Identify each relationship type and write all of the business rules.
A relationship described a connection that is found in entities. In the relationships there are several associations that are applied by database creators: One-to-many, Many-to-many and One-to-one.
The business rule connects to a brief, to the point description of a procedure or norm that is found in a precise company (Peter and Carlos 2007).
The table below shows the form of relationship that exists and the business rules in regards a school system where there are courses, classes and students.
Entity One Entity Two Relationship Type Business Rule
Course Class One-to-many A ‘Course’ has the ability to create several classes
Class Enroll One-to-many A ‘Class’ has the ability to ‘Enroll’ several students
Student Enroll One-to-many A ‘student’ has the ability to ‘enroll’ in a number of classes.
Courses Students Many-to-many Several ‘Courses’ has the ability to be taken by several ‘Students’
Student Course One-to-one A ‘Student’ has the ability to only take one ‘Course’
Haithcoat, T. (n.d). Relational Database Management Systems, Database Design and GIS. Acquired from: http://msdis.missouri.edu/resources/gis_advanced/pdf/Relational.pdf
Peter R. and Carlos C., 2007. Database Systems: Design, Implementation, and Management. Boston: Course Technology.
Ullman J. D. 1982. Principles of Database Systems. Computer Science Press: Rockville Maryland.
Wiederhold, G. 1983. Database Design. 2nd edition. McGraw-Hill: London, pp.