Database Application Development Assignment SQL Coding-Pearson BTEC Level 5 Higher National Diploma in Software Development (Edexcel HND)

Creating Tables

--create BRANCH table
Create Table BRANCH(
b_id varchar(10),
b_name varchar(50) not null,
location varchar(50) not null

Constraint Pk_Branch Primary key(b_id)
)


--create EQUIPMENT_AND_MATERIAL table
Create Table EQUIPMENT_AND_MATERIAL(
eq_id varchar(10),
life_time varchar(50),
eq_name varchar(50),
price money not null default 0.00,--decimal(18,2)
purpose varchar(50),
purchased_date date,
warranty_period varchar(20),
eq_description varchar(100),
eq_type char(2)

Constraint Pk_EQUIPMENT_AND_MATERIAL Primary key(eq_id)
)

--create ADMINISTRATOR table
Create Table ADMINISTRATOR(
e_id varchar(10),
e_fname varchar(50),
e_lname varchar(50),
nic_no varchar(30) not null unique,
entered_date date not null,
dob date not null,
gender char(1) default 'M' not null,
basic_salary money default 0.00 check(basic_salary>=20000),
hourly_rate money default 0.00,
b_id varchar(10)

Constraint Pk_ADMINISTRATOR Primary key(e_id),
Constraint Fk_ADMINISTRATOR foreign key(b_id)references BRANCH(b_id)
)


--create ADMINISTRATOR_ADDRESS table
create table ADMINISTRATOR_ADDRESS(
e_id varchar(10),
number varchar(10),
street varchar(20),
city varchar(20)

Constraint Pk_ADMINISTRATOR_ADDRESS primary key(e_id,number,street,city),