This is what i have done but i still have a problem:-
Can you correct my mistakes?
-------------------------------------------------------
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),
);