COIT20247 – DATABASE DESIGN
AND DEVELOPMENT
SQL(Creating data structures)
STUDENT NAME
Student Id
Theory Questions
Solution 1. SQL Data Definition Language (DDL) refers as SQL command used for creating,
Modifying and deleting a database table. These commands work on the table structure only.
DDL command includes the following tasks:
Create table – creates a new table
Drop table – drop an existing table
Alter table – Alter or modify a current table
Truncate table – Clear the table, i.e., to remove all records.
Rename table – Modify table name
Practical Questions
Solution 2. Created all the tables successfully. The tables created are Customer, Part, Order, and
OrderLine.
Solution 3. Yes, we have to create the tables in the particular order. This is because the order
table cannot be created before customer table and OrderLine table cannot be created before Order,
and Part table as the references of previous tables are added in the next table. If we try to create
Order and OrderLine table first, then this gives an error for not found the reference as the tables
do not exist. The Customer and Part tables can be created in any order.
Solution 4. The following data types are available in the Ms. Access database:
Short Text – up to 255 characters
Long Text – large text
Number – Numeric values including inter and decimal numbers
Date/Time – Date and time value
Currency – Currency to store Price values
Auto Number – a Unique index which is auto incremented.
Yes/No – One value from Yes or No
These are the basic data types commonly used in Ms. Access
Solution 5. Yes, from my view there is a need to add more constraints. The updated queries are
the following:
For Customer table:
CREATE TABLE Customer
(CustomerID LONG PRIMARY KEY,
FamilyName CHAR (50) not null,
GivenName CHAR (100) not null,
Street CHAR (250) not null,
City CHAR (20) not null,
State CHAR (20) not null,
PostCode INTEGER,
DateOfBirth DATETIME );
CREATE INDEX I1 ON Customer(PostCode);
CREATE INDEX I2 ON Customer(FamilyName);
CREATE INDEX I3 ON Customer(GivenName);
For Part Table:
CREATE TABLE Part
(PartID LONG PRIMARY KEY,
PartName CHAR (100) not null,
UnitPrice CURRENCY not null CHECK (UnitPrice >= 0),
QtyInStock INTEGER not null,
Category CHAR (150) not null);
CREATE INDEX I4 ON Part(PartName);
Order table
CREATE TABLE Order
(OrderID LONG PRIMARY KEY,
OrderDate DATE/TIME not null,
OrderTotal CURRENCY not null CHECK (OrderTotal >= 0),
CustomerID LONG not null,
CONSTRAINT FKOrderCust FOREIGN KEY (CustomerId) REFERENCES Customer );
CREATE INDEX I4 ON Order(CustomerID);
CREATE TABLE OrderLine
(OrderID LONG not null,
PartID LONG not null,
UnitPrice CURRENCY not null check (UnitPrice >=0),
QtyOrdered INTEGER not null,
QtySent INTEGER not null,
PRIMARY KEY (OrderID, PartID),
CONSTRAINT FKOLinePart FOREIGN KEY (PartID) REFERENCES Part,
CONSTRAINT FKOLineOrder FOREIGN KEY (OrderID) REFERENCES Order);
CREATE INDEX I5 ON OrderLine (OrderID);
CREATE INDEX I6 ON OrderLine (CustomerID);
Solution 6.
a) Alter table Customer add Column ContactPhone LONG;
b) Alter table Customer add Column Comments LongText;
c) Alter table Customer add Column CreditLimit Currency not null;
Solution 7.
a) Alter table Customer Drop Column ContactPhone;
b) Alter table Customer Drop Column Comments;
c) Alter table Customer Drop Column Currency;
Solution 8. I used three separate statements to add columns. Single alter statement to add three
columns is as follow:
Alter table Customer add Column ContactPhone LONG, Comments LongText, CreditLimit
Currency not null;
