I Hope That Someone Can Help Me, Askquestions Sql

Home Tutorialhelp Comment I hope that someone can help me

Comment

ahmed

I hope that someone can help me,
February 18, 2010 at 10:32 AM

This is Case Study:-

The University Accommodation Office Data requirements specification

Data requirements

----------------------------------------------------------------------------------------------Students--------------------------------------------------------------------------------

The data stored on each full-time student includes: the matriculation number, name (first and last name), home address (street, city, postcode), date of birth, sex, nationality, smoker (yes / no), special needs, any additional comments, current status (placed / waiting), and what course the student is studying on.

The student information stored relates to those currently renting a room and those on the waiting list. Students may rent a room in a hall of residence or student flat.

When a student joins the University, he or she is assigned to a member of staff who acts as his or her Advisor of Studies. The Advisor of Studies is responsible for monitoring the student's welfare and academic progression throughout his or her time at University. The data held on a student's Advisor includes full name, position, name of department, internal telephone number, and room number.

-----------------------------------------------------------------------Halls of residence--------------------------------------------------------------------

Each hall of residence has a name, address, telephone number, and a hall manager who supervises the operation of the hall. The halls provide only single rooms, which have a room number, place number, and monthly rent rate.

The place number uniquely identifies each room in all halls controlled by the Accommodation Office and is used when renting a room to a student.

-----------------------------------------------------------------------Student flats--------------------------------------------------------------------------

The Accommodation office also offers student flats. These flats are fully furnished and provide single-room accommodation for group of three, four, or five students. The information held on student flats includes a flat number, address, and the number of single bedrooms available in each flat. The flat number uniquely identifies each flat.

Each bedroom in a flat has a monthly rent rate, room number, and a place number.

The place number uniquely identifies each room available in all student flats and is used when renting a room to a student.

-----------------------------------------------------------------------Leases--------------------------------------------------------------------------------

A student may rent a room in a hall for various periods of time. New lease agreements are negotiated at the start of each academic year with a minimum rental period of one semester and a maximum rental period of one year, which includes Semesters 1, 2, and the Summer Semester. Each individual lease agreement between a student and the Accommodation Office is uniquely identified using a lease number.

The data stored on each lease includes the lease number, duration of the lease (given as semesters), name and matriculation number of the student, place number, room number, address details of the hall, and the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).

-----------------------------------------------------------------------Invoices--------------------------------------------------------------------------------

At the start of each semester each student is sent an invoice for the following rental period. Each invoice has a unique invoice number.

The data stored on each invoice includes the invoice number, lease number, semester, payment due, student's full name and matriculation number, place number, room number, and the address of the hall or flat. Additional data is also held on the payment of the invoice and includes the date the invoice was paid, the method of payment (cheque, cash, visa, etc.), and the date the first and second reminder is sent (if necessary).

-----------------------------------------------------------------------Student flat inspections-------------------------------------------------------------

Student flats are inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspection is the name of the member of staff who carried out the inspection, the date of inspection, an indication of whether the property was found to be in a satisfactory condition (yes or no), and any additional comments.

-----------------------------------------------------------------------Accommodation staff---------------------------------------------------------------

Some information is also held on members of staff of the Accommodation Office and includes the staff number, name (first and last name), home address (street, city, and postcode), date of birth, sex, position (e.g. Hall Manager / Administrative Assistant / Cleaner) and location (e.g. Accommodation Office or Hall).

-----------------------------------------------------------------------Courses--------------------------------------------------------------------------------

The accommodation Office also stores a limited amount of information on the courses run by the University including the course number, course title (including year), course leader, internal telephone number, room number, and department number. Each student is associated with a single course.

------------------------Next-of-kin----------------------------------------------------------------------------

Whenever possible, information on a student’s next-of-kin is sorted which includes the name, relationship, address (street, city, postcode), and contact telephone number.

------------------------------
And this is the Question About it :-
Based on the particular case study,

1. Provide a user’s requirements specification for the case study.

2. Draw Entity Relationship Diagram (ERD). Use ONLY UML NOTATION AS SHOWN IN THE TEXT BOOK. ANY OTHER NOTATION IS NOT ACCEPTED)

3. Create and validate a logical data model

4. List THREE (3) potential threats that could occur and propose countermeasures to overcome them.

-------------------------------------------------
So This is What i have done:- I want to know whether this requirement correct or no because some of my tables have problem but i couldn't solve it.

create database University_Accommodation_Office;
/*This is Student Table*/
Create Table Student
(
Matric_No nvarchar(10)not null, FName nvarchar(20)not null, LName nvarchar(20)not null,
DateOfBirth datetime, Sex nvarchar(1), Category_std nvarchar(15),
Nationality nvarchar(15), Smoker nvarchar(5), SpecialNeeds nvarchar(20),
Comments nvarchar(20), Status nvarchar(10), Course nvarchar(15),
Advisor_N0 nvarchar(5) not null, Room_No nvarchar(5)not null, Lease_No nvarchar(5) not null,
Invoice_No nvarchar(5) not null, Course_No nvarchar(5)not null, Address nvarchar(20),
Primary key (Matric_No) ,
Foreign key (Advisor_N0) references Advisor ,
Foreign key (Room_No) references Single_room ,
Foreign key (Lease_No) references Lease ,
Foreign key (Invoice_No) references Invoice ,
Foreign key (Course_No) references Courses
/*Foreign key (Kin_No) references NextOfKin,*/
);
----------------------------------------------------------------------------
/*This is Student Address Table*/
Create Table Std_Address
(
Matric_No nvarchar(15)not null, Street nvarchar(10), City nvarchar(20), PostCode nvarchar(20),
Primary key (Matric_No),
);
----------------------------------------------------------------------------
/*This is Addvisor Table*/
Create Table Advisor
(
Advisor_N0 nvarchar(15)not null,FullName nvarchar(20),Position nvarchar(20),
Dep nvarchar(20),Internal_Phone nvarchar(10),Room_No nvarchar(10)not null,
Primary key (Advisor_No),
Foreign key (Room_No) references Single_room not null,
);
----------------------------------------------------------------------------
/*This is Hall Tableنفذ*/
Create Table Hall_Of_Residence
(
H_Name nvarchar(15),H_Address nvarchar(20),Phone_No nvarchar(20),H_Manager nvarchar(20),Room_No nvarchar(5) not null,
Foreign key (Room_No)references Single_room,
);
----------------------------------------------------------------------------
/*This is Invoice Table*/
Create Table Invoice
(
Invoice_No nvarchar(15)not null,Sem_Pay_Due nvarchar(10),
Place_No nvarchar(10)not null,Hall nvarchar(20),Flat nvarchar(15),
Primary Key (Invoice_No)references Invoice not null,
Foreign Key (Lease_No) references Leases not null,
Foreign key (Room_No) references Single_room not null,
/*Foreign Key (Place_No) references Place not null,*/
);
----------------------------------------------------------------------------
/*This is Leases Tableنفذ*/
Create Table Leases
(
Lease_No nvarchar(15)not null,Duration nvarchar(10),Start_Date DateTime,End_Date DateTime,
Primary Key (Lease_No),
);
----------------------------------------------------------------------------
/*This is Single room Tableنفذ*/
Create Table Single_room
(
Room_No nvarchar(5)not null,Place nvarchar(10),MonthlyRentRate Decimal,
Primary Key (Room_No),
);
----------------------------------------------------------------------------
/*This is Student Flat Tableنفذ*/
Create Table Student_Flat
(
F_No nvarchar(5)not null,Address nvarchar(20),S_bedroom_No nvarchar(15),Lease_No nvarchar(15)not null,
Primary Key (F_No,S_bedroom_No),
Foreign Key (Lease_No) references Leases,
);
----------------------------------------------------------------------------
/*This is Single bedroom Tableنفذ*/
Create Table Single_bedroom
(
Room_No nvarchar(5)not null,MonthlyRentRate Decimal,Place_No nvarchar(10),Lease_No nvarchar(15),
Primary Key (Room_No) /*references Single_room not null*/,
Foreign Key (Lease_No) references Leases,
/*Foreign Key (S_bedroom_No) references Student_Flat,*/
);
----------------------------------------------------------------------------
/*This is Invoice Status Tableنفذ*/
Create Table Invoice_Status
(
Invoice_No nvarchar(5)not null,Paid_Date DateTime,Method nvarchar(10),ReminderOne nvarchar(10),ReminderTwo nvarchar(10),
Primary Key (Invoice_No),
);
----------------------------------------------------------------------------
/*This is Courses Tableنفذ*/
Create Table Courses
(
Course_No nvarchar(5)not null, Title nvarchar(10),C_Leader nvarchar(10),
Internal_Phone nvarchar(10),Room_No nvarchar(5), Dep_Name nvarchar(10),
Primary Key (Course_No),
Foreign key (Room_No) references Single_room,
);
----------------------------------------------------------------------------
/*This is Inspection Tableنفذ*/
Create Table Inspection
(
Inspection_No nvarchar(10)not null,Staff_Name nvarchar(10),Date DateTime,Sat_Condition nvarchar(10),Comment nvarchar(10),
Primary Key (Inspection_No),
);
----------------------------------------------------------------------------
/*This is Staff Tableنفذ*/
Create Table Staff
(
Staff_No nvarchar(10)not null,FName nvarchar(10),LName nvarchar(10),
Sat_Condition nvarchar(10),Comment nvarchar(10),Address_1 nvarchar(20),
Primary Key (Staff_No),
);
----------------------------------------------------------------------------
/*This is Staff Address Tableنفذ*/
Create Table Staff_Address
(
Staff_No nvarchar(15)not null,Street nvarchar(10),City nvarchar(20),PostCode nvarchar(20),
primary key (Staff_No),
);
----------------------------------------------------------------------------
/*This is Next-of-Kin Tableنفذ*/
Create Table NextOfKin
(
Matric_No nvarchar(10)not null,Kin_Name nvarchar(10),Relationship nvarchar(10),
Address_1 nvarchar(20), Phone_No nvarchar(10),Kin_No nvarchar(15),
Primary Key (Matric_No,Kin_No),
);
----------------------------------------------------------------------------
/*This is Next-of-Kin Address Tableنفذ*/
Create Table Kin_Address
(
Kin_No nvarchar(15)not null,Street nvarchar(10),City nvarchar(20),PostCode nvarchar(20),
primary key (Kin_No),
);

View All Comments | View Tutorial