Question

INSPECTOR InsID InsName INSPECTING InsID (FK) BuildingID (FK) Date Last DateNext MANAGER ManagerID MFName MLName MBDate MSalary MBonus (0) MRes BuildingID (FK) BUILDING BuildingID BNoOfFloors ManageriD (FK) APARTMENT AptNo Building ID (FK) ANOOfBedrooms CCID (O)(FK) CLEANING AptNo BuildingID SMemberID (FK) MANAGER PHONE MPhone ManagerID (FK) CORPCLIENT CCID CCName (U) CCIndustry CCLocation CCIDReferredBy (O)(FK) STAFF MEMBER SMemberID SMember Name

[IN SQL]

New to working in SQL and I need to make a database called HAFH. I am trying to create tables for the database with the information from the photo above but having issues.

I am looking for the syntax that successfully creates these tables, and references each other. I know each table has to be made in a specific order, and that is what I am struggling with.

Answer 1

This demonstration is using SQL Server.

create database HAFH;

/*use database to create tables*/
use HAFH;

--1.Table Name :Inspector
create table Inspector(
InsID char(3) primary key,
InsName varchar(15)
);

NOTE :HERE NEED TO CREATE EITHER MANAGER TABLE OR BUILDING TABLE AND THE USING ALTER TABLE SYNTAX ADD THE FOREIGN KEY , HENCE MANAGER TABLE IS CREATED FIRST.

--2.Table Name :Manager
create table Manager(
ManagerID char(4) primary key,
MFName varchar(15),
MLName varchar(15),
MBDate date,
MSalary numeric(9,2),
MBonus numeric(9,2),
MResBuildingID char(3)
);

--3.Table Name :Building
create table Building(
BuildingID char(3) primary key,
BNoOfFloors int,
ManagerID char(4),
foreign key (ManagerID) references Manager(ManagerID)
);

alter table Manager add foreign key (MResBuildingID) references Building (BuildingID);

--4.Table Name :CORPCLIENT
create table CORPCLIENT(
CCID char(4) primary key,
CCName varchar(25),
CCIndustry varchar(25),
CCLocation varchar(25),
CCIDReferredBy char(4),
foreign key (CCIDReferredBy) references CORPCLIENT(CCID));

--5.Table Name :Apartment
create table Apartment(
AptNo char(5) primary key,
BuildingID char(3),
ANoOfBedrooms int,
CCID char(4),
foreign key (BuildingID) references Building(BuildingID),
foreign key (CCID) references CORPCLIENT(CCID)
);

--6.Table Name :Inspecting
create table Inspecting(
InsID char(3),
BuildingID char(3),
DateLast date,
DateNext date,
foreign key (BuildingID) references Building(BuildingID),
foreign key (InsID) references Inspector(InsID)
);

--7.Table Name :StaffMember
create table StaffMember(
SMemberID char(4) primary key,
SMemberName varchar(15)
);

--8.Table Name :Cleaning
create table Cleaning(
AptNo char(5),
BuildingID char(3),
SMemberID char(4),
foreign key (BuildingID) references Building(BuildingID),
foreign key (SMemberID) references StaffMember(SMemberID),
foreign key (AptNo) references Apartment(AptNo)
);