Database schema

create table Student (
	ID integer not null ,
	Name as char(30) ,
	CNP as char(13),
	FacultyID integer,
	RegistrationNo integer,
	Address char(50),
	primary key (ID)
);

create table Faculty(
	ID integer not null ,
	Name as char(60) ,
	CityId integer,
	primary key (ID)
);


alter table Student add foreign key (FacultyId) references Faculty(ID) on delete set null on update cascade;

create table City (
	ID integer not null,
	Name char(30) not null,
	County char(30) not null,
	primary key (ID)
);

alter table Faculty add foreign key (CityID) references City(ID) on delete set null on update cascade;

create table Room (
	ID integer not null,
	Floor integer not null,
	NoBeds integer ,
	primary key (ID)
);

create table Item (
	ID integer not null ,
	ItemName char(20) not null,
	Primary key (ID)
);

create table Payment (
	ID integer not null,
	StudentId integer foreign key references Student(ID) on delete cascade on update cascade not null,
	AMount float not null,
	Date date not null,
	Delay integer,
	primary key (ID)
);

create table Staff (
	ID integer not null,
	Name char(30) not null,
	JobId integer ,
	Address char(50),
	primary key (ID)
);

create table Job (
	ID integer not null,
	Name char(20) not null,
	Description char(255),
	primary key (id)
);


alter table Staff add foreign key (JobID) references Job(ID) on delete set null on update cascade;

create table Dorm (
	ID integer not null,
	Name char(20) not null,
	CityID integer foreign key references City(ID) on delete set null on update cascade,
	Address char(50),
	Capacity integer not null,
	AvailableRooms integer,
	primary key (ID)
);

create table StudentInfo (
	StudentID integer,
	DormID integer foreign key references Dorm(ID) on update cascade on delete cascade,
	RoomID integer foreign key references Room(ID) on update cascade on delete set null,
	primary key (StudentID)
);

create table StudentItems (
	ID integer primary key,
	StudentID integer foreign key references Student(ID) on delete cascade on update cascade,
	ItemID integer foreign key references Item(ID) on delete cascade on update cascade,
);

create table RoomItems (
	ID integer primary key,
	RoomID integer foreign key references Room(ID) on delete cascade on update cascade,
	ItemId integer foreign key references Item(ID) on delete cascade on update cascade
);