ER modeling is an essential practice used by database experts on account of its simplicity and readability. Most CASE tools make use of the simple form and it is this simple form that is easy to learn and can be easily applied to a range of industrial and business applications. In addition to the above, it is also an extremely useful tool when used for communicating with the end-user about the conceptual model, and when used for the verification of suppositions made in the modeling process. The more complex form is of greater use to the experienced designer who aims to capture a greater semantic detail in diagramatic format, and to avoid the need to write a lengthy, tedious narrative so as to expound upon certain requirements and limitations in a repetitive fashion. It is the goal behind the database life cycle to show what steps are required in a systematic approach to database design - from logical design (which is independent of the system environment) to data distribution in a computer network, and to local physical design (which is based upon the details of the database management system that has been selected to utilise the database).
How to produce an ERD from a given case scenario?
Step by Step Procedure.
Step 1. Identify Entities:
The roles, events, locations, palpable things or ideas about which the end-users want to store data.
Step 2. Find Relationships:
Discover and understand the natural connections between pairs of entities using a relationship matrix.
Step 3. Draw a Rough ERD:
Position the entities in rectangles. The relationships should be situated upon line segments connecting the entities.
Step 4. Fill in Cardinality:
Determine the amount of occurrences of one entity for a sole occurrence of the related entity.
Step 5. Define Primary Keys:
Detect and recognise the data attribute(s) that uniquely identify one and only one manifestation of each entity.
Step 6. Draw Key-Based ERD:
Eradicate Many-to-Many relationships. Include primary and foreign keys in each entity.
Step 7. Identify Attributes:
Identify and title the information details (fields) which are vital and indespensible to the system under development.
Step 8. Map Attributes:
Each attribute should be matched exactly with one entity that it describes.
Step 9. Draw Fully Attributed ERD:
Modify the ERD from step 6 to account for entities or relationships discovered in step 8.
Step 10. Check Results:
Is the system data accurately depicted through the final Entity Relationship diagram?
Based upon the given scenario describe the process of Normalisation with diagrams up to 3NF and discuss the advantages and disadvantages of Normalisation. Provide ERDs for UNF, 1NF, 2NF, 3NF.
List the attributes of the entity.
Identify the main key (course code) - this must be unique.
Identify the repeating group of attributes
Identify the key from the repeating group
Remove the repeating group of attributes to form a new entity
Add it to the original key
We have two entities linked by the course code key after normalisation is performed at the 1NF stage; meaning that each student in the class is represented by an entry in the second table. This will eliminate the problems with fluctuating numbers of students.
The problem of the inability to store the document in database tables, has therefore been solved, but a new problem has been introduced. Examine table two in the 1NF column; the students' details (student id, name, date of birth, gender, contact number, date joined, and last attendance date) are stored in this table. However, the same information would be stored again within this table if the student enrolled on another course. A student's details only need to be stored once in our database. Some of the non-key attributes in this table are referenced by part of the table's key - not the whole key which is why this situation has occurred. The rule to deal with this problem is thus:
Tables with a composite key should be examined (composite key is a key made up of two parts)
Each non-key attribute shoulde be examined to determine if its key is the first part, or the second part. If neither, then the answer is both parts
Form a new table by removing the partial key and its dependents
Note that the student name, date of birth, gender, contact number and date joined are referenced by student id, while last attendance date is referenced by course code and student id (on account of the fact that it is the date for that student on that particular course).
Occasionally within an entity we can discover the existence of a 'key' and 'dependant' relationship between a group of non-key attributes. The existence of this type of relationship between Tutor Id and Tutor Name is quite obvious in table one of the 2NF stage. In this case they are removed to form a new table. If the 3NF conversion was not performed then the course tutor's details (in this case, name only) would be repeated every time this tutor's courses were stored.
The process is as follows:
Dependencies between non-key attributes in each table must be identified
Remove them to form a new table
Promote one of the attributes to be the key of the new table
In the original table t;his becomes the foreign key link (shown with a grey filled background).
The database is now complete!
Design an appropriate database for the given scenario.
The First table that I designed was the Student table. This table was made up of the following fields: Student ID, Student Name, Date of Birth, Gender, Contact Number, and Date Joined; I made the Student ID field the primary key field.
The second table that I designed was the Tutor Table. This table was made up of the following fields: Tutor ID, Tutor Name, Tutor Contact Details, Tutor Designation, and Tutor Skills; I made the Tutor ID field the primary key field.
The third table that I designed was the Course table. This table was made up of the following fields: Course Code, Course Name, Course Discipline, and Tutor ID; I made the Course Code field the primary key field and the Tutor ID field the foreign key field.
The fourth and final table that I designed was the Classlist Table. This table was made up of the following fields: Course Code, Student ID, and, Last Date of Attendance. I assigned a composite primary key to this table using both the Course Code field and the Student ID field.
The next step was to specify the relationships between all the tables. Referential Integrity was enforced for all relationships created, and the Cascade Update Related Fields and Cascade Delete Related Records checkboxes enforced.
Designs of four database reports that could be used by the organisation as described by the scenario. All reports must be created from complex database queries.
For the first query i set two complex conditions; the first condition was that the students be over 25 years of age, and the second that they must have enrolled for a course within the field of Computing and ICT. I also opted that only the following data for the entities that meet the above conditions are displayed within the report: Student ID, Student Name, Student Date of Birth, Student Gender, Date Student Joined, and Course Discipline. I used the following criteria and I am enclosing the expression in italics WITHIN the brackets and have made the relevant field type bold face: Date of Birth: (<=#01/01/1988#); Course Discipline: ("Computing and ICT"). The reason for the above query was so that the college can form a clear picture as to how many mature students (25 years of age or over) are currently enrolled at the college and undertaking a course within the field of computing and ICT.
For the second query i set two complex conditions; the first condition was that the students must have joined the college and enrolled on a course within the current academic year, and the second condition was that the student must have last attended the college within 2012, and not in 2013. I also opted that only the following data for the entities that meet the above conditions are displayed within the report: Student ID, Student Name, Student Gender, Student Contact Number, Date Student Joined, Course Code, Course Name, Last Date of Attendance. I used the following criteria and I am enclosing the expression in italics WITHIN the brackets and have made the relevant field type bold face: Date Joined: (>=#01/05/2012#); Last Date of Attendance: (<=#14/12/2012#). The reason for the above query was so that the college can see which of the students currently enrolled within the college that had joined and enrolled within the current academic year had not returned after Christmas.
For the third query i set two complex conditions; the first condition was that the students be of the female gender, and the second that the student be enrolled within any one of the following course disciplines: Bricklaying, Carpentry, Construction and Building Service, Electrical and Electronic, Plumbing, Painting and Decorating, or Welding and Fabrication. I also opted that only the following data for the entities that meet the above conditions are displayed within the report: Student ID, Student Name, Student Date of Birth, Student Gender, Student Contact Number, Date Student Joined, Course Code, Course Name, and Course Discipline. I used the following criteria and I am enclosing the expression in italics WITHIN the brackets and have made the relevant field type bold face: Gender: ("Female"), Course Discipline: ("Bricklaying" Or "Carpentry" Or "Construction" Or "Building Services" Or "Electrical and Electronic" Or "Plumbing" Or "Painting and Decorating" Or "Welding and Fabrication"). The reason for the above query was so that the college can see how many female students are enrolled on a course within a field which is usually targeted by the male gender.
For the fourth and last query i set three complex conditions. The first condition was that the the students joined the college in the years 2009, 2010, 2011, or 2012; the second condition is that they are under 25 years of age; and the third condition is that they are enrolled on a course that is within the field of Higher Education. I also opted that only the following data for the entities that meet the above conditions are displayed in the report: Student ID, Student Name, Student Date of Birth, Student Gender, Student Contact Number, Date Student Joined, and Course Discipline. I used the following criteria and I am enclosing the expression in italics WITHIN the brackets and have made the relevant field type bold face: Date of Birth: (<=#01/01/1988#), Date Joined: (>=#01/09/2009# And <=#01/09/2012#), Course Discipline: ("Higher Education"). The reason for the above query was so that the college can see how many students, under the age of 25, that had joined the college in either 2009, 2010, 2011, or 2012 were enrolled on a course within the field of Higher Education.
Test the system using an appropriate test plan, test data and test report. Amongst other things testing must demonstrate that referential integrity and data integrity has been enforced and relevant reports have been developed successfully.
I began my tests within the Student Input Form; The first field I tested was Students Name; I tried to enter the numbers 123 and see if the verification rule I set that allowed only letters and spaces was correct; I received the expected warning prompt. I then tested the Date of Birth field; I tried to enter the letters aaa and see if the verification rule I set was correct; I again received the expected warning prompt. I then moved on to the next field, Gender; I tried to enter the numbers 1234 and see if the verification rule I set which allowed the expressions Male or Female only was correct and I received the expected response. Within the next field, Contact Number I attempted to enter the letters huyt and received the expected warning prompt, for I enforced the verification rule that only allowed the entering of the numbers 0 to 9 within the field. After also testing the last field, Date Joined. As an additional note on the subject i also carried out a Test Validation Rules Request which was successful.
After the Student Input Form; I moved on to the Tutor Input Form. I used the same validation rule expressions for the fields Tutor Name and Tutor Contact Number and all tests were successful and as expected. After testing the last field, Tutor Skills, I successfully created the first tutor record. Again, as an additional note on the subject i also carried out a Test Validation Rules Request which was successful.
The third test I conducted was within the Course Input Form; I did not enforce any validation rules within this table, however, I did opt to create a combo box instead of an ordinary text-field so that my job was made easier during the whole test data entry process. There were no surprises here either and the whole record creation process was successful; the combo box sent the correct data to the Course table as was expected.
The fourth test I conducted was within the Classlist Input Form; again, there were no problems during the process, however I did note a weakness in the current setup which could lead to possible data integrity problems in the long run; I will touch upon this later. The record creation was successful.
Following these succesful tests I went ahead and created a number of records within all four tables. This provided the environment needed to test all four database reports I prepared earlier from complex database queries; all tests conducted were successful.
Evaluate the effectiveness of the database solution and suggest methods for improvement.
After having closely scrutinised the database solution at hand, in a sentence I would say that its effectiveness - as it stands is at best mediocre. With a little improvement in a number of areas which I will now attempt to touch upon, it can without a doubt increase in effectiveness. The first and most obvious flaw with the current database is a complete lack of essential data of which a college in this modern day and age would most definitely require - MORE information! For example, within the Student Table an email field can be introduced along with a second contact number, for in todays modern age nearly everyone has a mobile contact number as well as a residential contact number; the introduction of address fields are also necessary extensions; in the Student Input Form the introduction of an image place-holder is also a must.
Within the Tutor Table, as also in the Student Table, an email address and second contact number are necessary, as well as the introduction of address fields. Most importantly however, the Tutor Designation and Tutor Skills fields, in their current form - are fairly useless; their usefulness would increase greatly with the use of either a combo box or a list box style field where there are a wide range of relevant (to an educational establishment) preset values; these two fields are too vague as they stand and allow the entering of any kind of information - including the useless kind. Again, the Tutor Input Form could do with an image placeholder; the need is not as great as that of the students for there are never as many teachers as students at these establishment types however it would still be a worthy addition.
The Classlist Table in my opinion in its current state and form - has a major flaw; for example, if we take into account that the Student Table requires entry of the date a student joined the college for any new record created, the Classlist Table, when prompting the user to enter a value in the Date of Last Attendance field - cannot, in its current state - compare the value of the entry to the value that was entered for the date joined of the student in question within the Student Table; so in theory one could enter a value within the field Date of Last Attendance which is earlier in date than the value Date Joined in the Students Table, without any kind of error prompt.
In the last table; the Course table, a possible improvement would be the introduction of a combo box or list box for the two fields Course Code and Course Name, that are reliant upon the data entered within the course discipline field.
As part of extending upon the database solution that has already been prepared; I decided to enforce some of the suggestions I made in task 5. I added the following fields to the both the Student and Tutor tables: the Landline field, the Email Address field, the House/Number Name, the Street Name, the City field, the Postcode field, and the Date Joined field.
I also opted to add these fields to both the Student and Tutor Input Forms, along with image place-holders; the image of a student or tutor may be added.