Question

Created the database and all the tables in phpMyadmin. In addition, write all the sql statements used in perforing the tasks in a word document and submit that for grading.

Lab 1: CREATE STATEMENT

This statement is used to create a database or a table.

1. CREATE DATABASE

This statement is used to create a database

Syntax:

CREATE DATABASE database_name; OR

CREATE SCHEMA database_name;

Example:

CREATE DATABASE Nyumbani; OR

CREATE SCHEMA Nyumbani ;

2. CREATE TABLE

This SQL statement is used to create a table in a relational database.

Syntax:

CREATE TABLE table_name ( Column 1 datatype [PRIMARY KEY], Column 2 datatype [NOT NULL],

:

Column n datatype

);

create table branch(

branch_no int not null primary key, br_street varchar(25) not null, br_area varchar(20),

br_town varchar(20) not null, br_pcode int not null, br_telno varchar(12)

);

Practical 1

Create the following tables using the described properties below;

1. staff

Column name	Data type	Size	Properties	Nullity
staff_no	INT	-	PRIMARY KEY	NO
branch_no	INT	-	FOREIGN KEY (References branch(branch_no)	NO
staff_surname	VARCHAR	20	-	YES
staff_othernames	VARCHAR	30	-	NO
staff_street	VARCHAR	25	-	NO
staff_town	VARCHAR	20	-	NO
staff_pcode	INT	-	-	NO
staff_telno	VARCHAR	12		YES
staff_gender	VARCHAR	1		NO
staff_salary	DOUBLE	-	-	NO

create table staff

(staff_no int primary key,

branch_no int references branch(branch_no), staff_surname varchar(20),

staff_forenames varchar(30) not null , staff_street varchar(25) not null, staff_town varchar(20) not null, staff_pcode int not null,

staff_telno varchar(12), staff_gender varchar(1) not null, staff_salary money

);

1. owner

Column name	Data type	Size	Properties	Nullity
owner_no	INT	-	PRIMARY KEY	NO
ow_surname	VARCHAR	20	-	NO
ow_forenames	VARCHAR	30	-	YES
ow_town	VARCHAR	20	-	NO
ow_pcode	INT	-	-	NO

2. property_type

Column name	Data type	Size	Properties	Nullity
prop_type	INT	-	PRIMARY KEY	NO
prop_type_description	VARCHAR	20	-	NO

Answer 1

CREATE TABLE `staff` (
`staff_no` int NOT NULL AUTO_INCREMENT,
`branch_no` int NOT NULL,
`staff_surname` varchar(20) DEFAULT NULL,
`staff_othernames` varchar(30) NOT NULL,
`staff_street` varchar(25) NOT NULL,
`staff_town` varchar(20) NOT NULL,
`staff_pcode` int NOT NULL,
`staff_telno` varchar(12) DEFAULT NULL,
`staff_gender` varchar(1) NOT NULL,
`staff_salary` double DEFAULT NULL,
  
PRIMARY KEY (`staff_no`),
FOREIGN KEY (branch_no) REFERENCES branch(branch_no)
)

CREATE TABLE `owner` (
`owner_no` int NOT NULL AUTO_INCREMENT,
`ow_surname` varchar(20) NOT NULL,
`ow_forenames` varchar(30),
`ow_town` varchar(20) NOT NULL,
`ow_pcode` int NOT NULL,
  
PRIMARY KEY (`owner_no`)
)

CREATE TABLE `property_type` (
`prop_type` int NOT NULL AUTO_INCREMENT,
`prop_type_description` varchar(20) NOT NULL,
  
PRIMARY KEY (`prop_type`)
)