Umar Jamil cs03uuj (0309811) Database System Design Application 28/04/2007
The aim of this assignment was to create a Database based on a case study scenario on a language summer school. A successful database system had to be implemented holding key information and then querying the database using at least 6 queries.
User requirements:
- The summer school employs 20 members of staff. The staffs are allocated different jobs including managerial roles. Hall managers & office managers are assigned.
- The student records are kept and detained even when they are placed on a waiting list, so that they can be recognised when wanted.
- The Renting period can be between 1-12 weeks. The students can pay on a weekly basis. The minimum agreement is 1 week & the maximum being 12 weeks.
- The manager needs to know the duration of the lease (i.e. weeks), the accommodation details and the student details
- At the start of each week each student is sent an invoice for the following rental period.
Identify Entities and their Attributes
Entities & their role
Below are the entities listed and their main roles. This helps the users to understand what part each entity plays in the system.
The E-R Model and the Assumptions
1..1 1..1
This relationship shows that a student can have just one place on the waiting list and that the waiting list can hold 1 specific student.
1..1 0..*
The assumption here is that a student can hold one lease agreement and a no lease Agreements can still have many students in the system.
0..* 1..1
The Assumption here is that a Lease agreement can have many invoices sent every week but a certain invoice can only apply to one accommodation.
0..* 1..1
The assumption here is that no lease agreement is needed but still a lease can hold many accommodations but one accommodation can have one lease agreement as its for one room only.
1..1 *..1
The assumption here is that one staff is contracted to one lease Agreement but many lease agreements have just one staff contracted to them
0..1 1..*
The assumption here is that accommodations contain a hall but one hall can have many accommodations
0..1 1..*
The assumption here is that accommodations contain a flat but one flat can have many accommodations
Relationships
This is the actual E-R Model. It clearly shows the relationship type and the extent of the relationship. The flow of the relationship is also indicated and the cardinality too.
The boxes represent the entities, while the straight line defines the strength of the relationship. The arrowhead is pointing in the direction the information is flowing and forms the basis of the relationship. Next to the arrowhead is the name of the arrow, which has to be ...
This is a preview of the whole essay
Relationships
This is the actual E-R Model. It clearly shows the relationship type and the extent of the relationship. The flow of the relationship is also indicated and the cardinality too.
The boxes represent the entities, while the straight line defines the strength of the relationship. The arrowhead is pointing in the direction the information is flowing and forms the basis of the relationship. Next to the arrowhead is the name of the arrow, which has to be a verb or noun. Below the straight line each entity has a numerical value suggesting its type of relationship. The 0 represents there is no relationship present. The 1 represents it is true for just 1 circumstance and the * represents that the relationship is true to most circumstances.
It is essential to remove any chasm traps before creating a E-R model. A chasm trap is when a model suggests the existence of a relationship between entity types, but the pathway does not seem to be active or evident between certain entity occurrences.
Placed
1..1 1..1
1..1
Agrees
Controls 0..* Associated
1..1 *..1 0..* 1..1
0..*
Leased
1..1
Contains Includes
0..1 1..* 0..1 1..*
The Main entities to be identified are:
- Student
- Staff
- Accommodation
- Invoice
- Lease agreement
- Halls
- Flats
- Waiting List
These are the attributes for the given entities:
- Student: Student ID, title, Surname, First name, address line1, address line2, Town/city, postcode, date of birth, sex, category, course code, email, telephone.
- Staff: Staff ID, title, Surname, First name address Line1, address Line2, Town/City, date of birth, sex, Position, staff wage, email, telephone.
- Accommodation: Accommodation number, Staff ID, address Line1, Address Line2, Town/ City, telephone, accommodation type, place number, weekly rent.
- Invoice: Invoice number, Invoice address, Student ID, Place number, Accommodation number, Accommodation type, Invoice date, Invoice Status, payment due, payment date, payment amount, payment method.
- Lease agreement: Lease Number, Accommodation Number, Place number, Lease duration, Student ID, Staff ID, Lease start, Lease End.
- Halls: Place Number, Accommodation number, Weekly rate, Staff ID
- Flats: Place Number, Accommodation number, weekly rate, Staff ID
- Waiting List: Student ID, Waiting position, waiting status, registration number
Keys:
The entities shown above just define the attributes but the keys aren’t defined. Keys are essential to the database system because without them the data searched could not be refined further. Problems may exist were two people may have the same name and may live at the same address. To solve this problem a unique key is given. Each entity has to have a unique identifier and a way to be recognised and called upon in the database.
The table below shows all the keys present in each entity. The foreign keys are taken from the entity because it is a primary key elsewhere in another table. When the primary key isn’t available the identifier becomes the foreign key & then the composite key.
Conceptual Model
This is a conceptual model. The purpose of this model is to show all the entities and their relationships alongside primary & foreign keys. It also shows the flow of the relation ship and the cardinality too. It is advanced from a ER model because it can be used to work out what attributes are used where and what are the primary keys required. The foreign keys help identify were the entities will be linked specifically and the cause of that link.
Placed
1..1 1..1
1..1
Agrees
0..*
Controls Associated
0..* 1..1
1...1 *..1
0..* Leased
1..1
Contains Includes
0..1 1..* 0..1 1..*
Data Redundancies
The Idea behind the data redundancy is the fact that entities cannot rely on each other for them to be accessed. If a entity B could only be accessed or used via entity A that has to be collaborated so that Entity A & Entity B become one Entity C. If an entity was present were it could only be used in the system from another entity then it should be made redundant.
Placed
1..1 1..1
1..1 Agrees
0..*
Associated
Controls
1..1 *..11
0..* 1..1
0..*
Leased
1..1
Contains Includes
0..1 1..* 0..1 1..*
To design a logical model was the next stage. In order to do this we had to make sure from the conceptual model we removed any: Data Redundancies, Removing m:n relationships, removing complex relationship, removing recursive relationships, removing relationships with attributes, re-examine 1:1 relationships. These are the differences when creating a model from a conceptual to a logical one.
Logical Model: This is a Logical Model:
1..1
Agrees
0..*
Controls Associated
1..1 *..1 0..* 1..1
0..* Leased
1..1
Includes
0..1 1..*
Normalisation
When we normalise we aim to keep tables with no interrelating data but has one and only one value. The forms start from 0NF (0 Normal form), which mean unnnormalised form and all the data is raw just like the attributes described on page 2. Unnormalized forms can have many relating and repeating data that’s why a 1NF (1st Normal Form) is created.
This is an unnormalized table:
0NF
Student: (Student ID, title, Surname, First name, address line1, address line2, Town/city, postcode, date of birth, sex, category, course code, email, telephone).
Staff: (Staff ID, title, Surname, First name address Line1, address Line2, Town/City, date of birth, sex, Position, staff wage, email, telephone.)
Accommodation: (Accommodation number, Staff ID, address Line1, Address Line2, Town/ City, telephone, accommodation type, place number, weekly rent.)
Invoice: (Invoice number, Invoice address, Student ID, Place number, Accommodation number, Accommodation type, Invoice date, Invoice Status, payment due, payment date, payment amount, payment method.)
Lease agreement: (Lease Number, Accommodation Number, Place number, Lease duration, Student ID, Staff ID, Lease start, Lease End).
Halls: (Place Number, Accommodation number, Weekly rate, Staff ID)
Flats: (Place Number, Accommodation number, weekly rate, Staff ID)
Waiting List: (Student ID, Waiting position, waiting status, registration number)
A 1NF is used so that the appropriate data is entered in empty columns of rows containing the repeating data. The other reason is to replace the repeating data along the copy of the original key attribute in a separate relation.
1NF
Student: (Student ID, title, Surname, First name, address, date of birth, sex, category, course code, email, telephone).
Staff: (Staff ID, title, Surname, First name address Line1, address Line2, Town/City, date of birth, sex, Position, staff wage, email, telephone.)
Accommodation: (Accommodation number, Staff ID, address Line1, Address Line2, Town/ City, telephone, accommodation type, place number, weekly rent.)
Invoice: (Invoice number, Invoice address, Student ID, Place number, Accommodation number, Accommodation type, Invoice date, Invoice Status, payment due, payment date, payment amount, payment method.)
Lease agreement: (Lease Number, Accommodation Number, Place number, Lease duration, Student ID, Staff ID, Lease start, Lease End).
Halls: (Place Number, Accommodation number, Weekly rate, Staff ID)
Flats: (Place Number, Accommodation number, weekly rate, Staff ID)
Waiting List: (Student ID, Waiting position, waiting status, registration number)
2NF
When a table is normalised to 1st form it still may contain irrelevant and partial data. If this is the case it is time to normalise to 2NF. This is all the given attributes rely on the primary key so that they are fully functional.
3NF
When a table is normalised to third normal form (3NF) it means it is 2NF + dependencies on the primary key, so that every data is arranged accordingly.
Student: (StudentID, Surname, address, telephone, date of birth, FirstName, title, sex, category, course code, email, waiting status, waiting position).
Staff: (StaffID, Surname, address, telephone, FirstName, DOB, title, sex, Position, staffwage, email.)
Accommodation: (AccommodationNumber, StaffID, address Line1, Address Line2, Town/ City, telephone, accommodation type, place number, weekly rent.)
Invoice: (InvoiceNumber, StudentID, PlaceNumber, AccommodationNumber, Invoice address Accommodation type, Invoice date, Invoice Status, payment due, payment date, payment amount, payment method.)
Lease agreement: (LeaseNumber, AccommodationNumber, PlaceNumber, Student ID, Staff ID, Lease duration, Lease start, Lease End).
Room: (PlaceNumber, StaffID, AccomodationNumber, WeeklyRate)
SQL Queries
The role of the query is to question the database as to ask a question so the database can bring up the right results. In SQL querying the database can be done from going go the query box and typing in your selected query.
This is Query 1:
Select * from Staff
Where Sex like 'Female';
This Query searches the database and brings up all the record from the Staff folder who is Females. The highlighted orange boxes on the record shows these were the fields we called upon to be checked and viewed. Below only females were listed and results came up showing the number of female staffs available. This is important for Diversity and surveying reasons.
Query 2:
Select * from Student
Order by Surname
Users can use this query so that they can search all the students from their surname alphabetically. This is a useful thing to do because it helps to arrange all the records in order in case they need to be printed of and viewed. Managers at high level could use the Surname so they can easily search for a specific employer. Reports could be drawn up and reviewed using this query.
Query 3:
select * from Staff
where StaffSalary >= 25000
This Query is used to show all the big time earners inside the language summer school. This corresponds to the staff table and is asking to list only those employees who have an annual salary of more then 25K. This query can be used for different values of salaries to see who earns high amounts in wage and who earns a low amount in wage.
Query 4:
select * from LeaseAgreement
where LeaseDurationweeks >= 4
This Query Shows and lists all the Students from the LeaseAgreement table who are staying in Accommodation for more then 4 weeks. The same could be done for between 1-12 weeks so that all those still left after a period of time can be seen. This query can be used to define who is still staying after certain weeks and who has to leave their accommodation. This query helps managers to develop reports on how long a student is likely to stay in their accommodation for and what the average staying period is.
Query 5:
Select * from Invoice
Where InvoiceStatus like 'Unpaid'
This Query asks the question: If students have not paid their Invoices list them accordingly and show the results. The table below shows all the Students who have not paid their invoices and how much they still owe in the PaymentDue field. This Query is important because the system can narrow down and track down anyone who still has to pay of his or her rent and it could determine what the next step will be in order to receive the payment.
Query 6:
select * from Staff
where StaffSalary not between 10000 and 40000
This Query asks the question: Are there any members of Staffs not earning between 10k and 40k. This Query is used to see who doesn’t earn in a certain limit and who earns excessively or those underpaid. It uses a +- system so it becomes not between. The results are listed and the query is working as the orange box around the StaffSalary field shows the only two members of staffs earning the big purse.
Evaluation:
Problems:
The problems I encountered were more involved with the lack of planning involved in this coursework. I believed I tried working to the best of my ability but if I planned it better I would of more likely did it progressively. The other problem faced was the busy time schedule I had. With so many things on my mind I felt if I had a clear head when doing this coursework I would have been more confident on finishing it on time. The most difficult step was learning all the new concepts in SQL and learning the software in a given set time. I had only previously done projects in Access. I found it difficult to use my access skills in SQL. I tried putting my knowledge to practise but it didn’t work out, as I felt SQL had different techniques to access.
Solutions:
In future I would recommend students to plan ahead and think ahead. When the coursework is set students should start drawing out plans on how they will manage their coursework and what they will include. I would then strongly advise the students to play around with the software if they have not used it before and practise doing sample tables. I also suggest for students to buy a book on database & familiarise themselves with what database is about. The scenario sheet should also be read so that students know what is required from them.
The strengths of these solutions are that they come from experience & are something I would like to do from now on. I have learnt that leaving work to the last minute or rushing work doesn’t help. The fact of the matter is planning and writing out what you want to do makes you best remember what you are planning to do so acts as reinforcement for you to work harder. The other important thing is that buying yourself the book and reading it effectively actually upgrades and specialises your knowledge adding key weight to your outcomes.
The weakness of my solutions are that: in the real life no-one compiles by planning, some people are all out attack and do everything on the dot without planning and still manage to achieve what they want to do. The fact is that some people adopt their own technique best suited to them & only when that technique may not work they might try new coursework techniques.
Improvements to my system:
The improvements to my systems could be normalising them better inside the tables in SQL. The level of data entry was also low and if would have been essential to have more students available. The amount of rooms that were available was less then what there should have been. The Accommodation table shows the foreign keys in two different places instead of being one after the other. O improve my system I would make sure I normalise my table to 3NF properly next time and I Will make sure I have a consistent amount of data to test with. I will also get rid of the more redundant data and make sure I have a successful database with no repeating fields.
In addition to my System I will add more drop boxes and add more selection boxes and reduce the table down to relevant data i.e. course code doesn’t apply to the system or the boundary.
References:
Connolly, T. and Begg, C. Database Systems: A Practical Approach to Design, Implementation and Management, Addison Wesley, 2001, Third Edition.
