Dormitory database design free essay sample

Introduction A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring this information. The term database is correctly applied to the data itself, and is different from the DBMS which is a software system that allows to store and change the data. 1.General View of the Database 1.2. Aim of the Database The University of Bogazici needs to improve the database information of student dormitory, the details are: 1. The University has three floors. Each floor has six houses. Each houses contains two rooms.Each Room in first and second floor has six student while third floor has four student in each room. 2. There are three types of staffs who responsible for the student.They are three security, one manager and three teacher. We will write a custom essay sample on Dormitory database design or any similar topic specifically for you Do Not WasteYour Time HIRE WRITER Only 13.90 / page 3. There are three types of service in the dormitory; chef to cook meal for the students, laundry to wash and iron the student uniform and cleaning.There are 3 chef and laundry service and also 6 cleaning service. 1.3. View of the Dormitory 2.Tables 2.1. House This table holds HouseID and gender informaton. According to this table: *Girls stay in 100, 101, 102, 200, 201, 202,300 ,301 ,302 Houses. *Boys stay in 103,104,105,203,204,205,303,304,305 Houses. 2.3.Payment Types This Table holds information about payment that student charge for. Payment types can change according to air conditioning and number of people stay in rooms.For Example if you want to stay in 4 person room with air conditioning , you have to pay 350 TL per month. 2.4.Room This Table holds information about is there any air conditioning in room or not and also what is the capacity of room. 2.5.Room Service This Table holds information about which room takes which services. 2.6.Salary This Table holds information about montly salaries of service and staff who responsible for dormitory. 2.7.Service This Table holds information about service personnel’s name surname and photos. 2.8.Staff This Table holds information about staff’s name surname and photos. 2.9.Student This Table holds all of the information(name,surname,birthdate,city,phone,gender,houseID and roomID) about students who stay in dormitory. 3.Queries 3.1.Age Student Finds the ages of the each student. 3.2.Alphabetical List of Students Sorts students according to alphabetic order. 2.3.Delete a Student Ä °f you enter student ID you want to delete, you can find and delete the related record easily. 2.4.CrossTab City Order How many student who stay in each room comes from same city.For example in house100 there are 5 student comes from istanbul. 2.5.Parameter Query When you enter a houseID such as 102 ,103 or 305 , rooms and students in these Houses are displayed.For example I entered 302 as houseID. Display students who stays in 302 . 2.6.Quarterly Student Birth Finds students’ quarterly birthdate with a crosstab query. 2.7.Room with Price Charges and details for each room. 2.8.Select Query Displays names between [A-K]* and female ones. 2.9.Service and Staff Salaries Displays salaries of each staff and service personnel. 2.10.Service Type Which person is responsible for which service(laundry cleaning or chef) 2.11.Student According to city It is a parameter query which holds city information.Ä °f you enter AydÄ ±n,for example, you will see student detail who comes from AydÄ ±n. 2.12.Ten Oldest Students This query was made by SQL codes. SELECT TOP 10 Student.StudentID, Student.FirstName, Student.LastName, Student.Gender, Student.Birthdate FROM Student ORDER BY Student.Birthdate; 2.13.Update Payment for Rooms Updates payments for each room using a formula IIf([Capacity]=6;IIf([Air]=Yes;2;1);IIf([Air]=Yes;3;4)) 2.14.Update Salary Ten Percent When you enter a department you want to increase salary,it will be increased by ten percent. 4.Forms 4.1.Salary Chart Display salaries as pies 4.2.Room Service Room information with tab control of students and service types. 4.3.Staff Information Holds information details about staffs and resposibilities. 4.4. Student Student informations and command buttons. 4.5.Tabular Student Information Student details as tabular order. 4.6.RoomService subform Subforms: They were used in related forms as a subform. 4.7.HouseStaff subform