COM4003 Database Design

• There exists different categories of the antiques and every category is stored.
• An item can be of only one category.
• An item does can be valued, restored or repaired. These actions can be done on an item and they are only done by one expert.
• Specialty antique search has many experts who are perform can either perform valuation, restoration or repair. Each expert performs only one action.
• The business can buy one or more items in a purchase.
• The business can sell one or more items in a sale.
• Each purchase involves a specific seller.
• Each sale involves only one buyer.
• The business can lease an item to a client. Each lease record is for a single item.
• The business can retain an item and allocate the reserved item to a member.
• THe business accrues different types of expenses and each expense results to one payment. A payment can be done using only one payment method.
• Question 2
• 3NF relations

• Category (categoryID, name)
• Item (itemID, categoryID, name, costPrice, sellingPrice, description)
• Seller (sellerID, firstName, lastName, phoneNO, email, type)
• Buyer (buyerID, firstName, lastName, phoneNO, email)
• Sales (saleID, date, buyerID)
• Sales_items (saleID, itemID)
• Purchase (purchaseID, date,sellerID)
• Purchase_items (itemID, purchaseID)
• Item_actions (actionID, itemID, date, expertID, csot, type)
• Lease (leaseID, itemID, period, client, cost)
• Expert (expertID, firstName, lastName, type, email, phone, addres)
• Expenses (expenseID, type, amount, date)
• Collection (collectionID, itemID, staffID, dateRetained)
• Staff (staffID, firstName, lastName, email, phone, position)
• Payment (paymentID, expenseID, date, paymentMethod)

Normalization

The normalization process involved performing the following checks on each entity from the entity relationship diagram.

• Verifying 1NF- this step involves ensuring that an entity has repeating groups and if there are repeating groups then they are removed.
• Verifying 2NF- This step involves ensuring that an entity has no partial dependencies. This is done by making sure that an entity has a candidate key and no combination of two or more attributes exist as a candidate key.
• Verifying 3NF- This step involves ensuring that an entity has no transitive dependences. This is done by making sure that the entity has one primary key which is used to functionally determine the other attributes.

Normalization of the entities is done using bottom up approach for all the entities specified in the entity relation diagram. Thus to demonstrate the normalization process for each entity involves the following steps;

• Category (categoryID, name)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is categoryID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is categoryID. categoryID determines all the other attributes

• Item (itemID, categoryID, name, costPrice, sellingPrice, description)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is itemID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is itemID. itemID determines all the other attributes

• Seller (sellerID, firstName, lastName, phoneNO, email, type)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is sellerID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is sellerID. sellerID determines all the other attributes

• Buyer (buyerID, firstName, lastName, phoneNO, email)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is buyerID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is buyerID. buyerID determines all the other attributes

• Sales (saleID, date, buyerID)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is saleID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is saleID. saleID determines all the other attributes

• Sales_items (saleID, itemID)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is the combination of saleID and itemID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is the combination of saleID and itemID.

• Purchase (purchaseID, date,sellerID)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is purchaseID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is purchaseID. purchaseID determines all the other attributes

• Purchase_items (itemID, purchaseID)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is the combination of itemID and purchaseID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is the combination of itemID and purchaseID. categoryID determines all the other attributes
  • Item_actions (actionID, itemID, date, expertID, csot, type)
    • 1NF- This relation is in 1NF because it has no repeating groups.
    • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is actionID.
    • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is actionID. actionID determines all the other attributes

• • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is leaseID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is leaseID. leaseID determines all the other attributes

• • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is exertID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is expertID. expertID determines all the other attributes

• Expenses (expenseID, type, amount, date)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is expenseID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is expenseID. expenseID determines all the other attributes

• Collection (collectionID, itemID, staffID, dateRetained)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is collectionID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is collectionID. collectionID determines all the other attributes

• Staff (staffID, firstName, lastName, email, phone, position)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is staffID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is staffID. staffID determines all the other attributes

• Payment (paymentID, expenseID, date, paymentMethod)
  • 1NF- This relation is in 1NF because it has no repeating groups.
  • 2NF- This relation is in 2NF because no partial dependencies exist. It has only one candidate key which is paymentID.
  • 3NF- This relation is in 3NF because no transitive dependencies exist in the relation. The relation has only one key attribute which is paymentID. paymentID determines all the other attributes

Question 3

Implementation of the database was done MySQL which is accessed from PHPMyAdmin using a browser. Below is the code for each table and a screenshot of the structure of the table.

• Category table

Code

-- User Id: 227171

create table category(

    categoryID integer primary key,

    name varchar(50) not null

• Item table

Code

-- User Id: 227171

create table item(

    itemID integer primary key,

    categoryID integer not null,

    name varchar(50) not null,

    costPrice decimal(10,2) not null,

    sellingPrice decimal(10,2) not null,

    description varchar(500) not null,

    foreign key (categoryID) references category (categoryID),

    check (costprice>0),

    check (sellingPrice>0)

• Seller table

Code

-- User Id: 227171

create table seller(

    sellerID integer primary key,

    firstName varchar(50) not null,

    lastname varchar(50) not null,

    phoneNo varchar(25) not null,

    email varchar(50) null,

    type varchar(25) not null

• Buyer table

Code

-- User Id: 227171

create table buyer(

    buyerID integer primary key,

    firstName varchar(50) not null,

    lastname varchar(50) not null,

    phoneNo varchar(25) not null,

    email varchar(50) null

Table sales

Code

-- User Id: 227171

create table sales(

    saleID integer primary key,

    saleDate date not null,

    buyerID integer not null,

    foreign key (buyerID) references buyer (buyerID)

• Purchase table

Code

-- User Id: 227171

create table purchases(

    purchaseID integer primary key,

    purchaseDate date not null,

    sellerID integer not null,

    foreign key (sellerID) references seller (sellerID)

• Sales_items table

Code

-- User Id: 227171

create table sales_items(

    saleID integer not null,

    itemID integer not null,

    primary key (saleID,itemID),

    foreign key (saleID) references sales(saleID),

    foreign key (itemID) references item (itemID)

• Purchases_items table

Code

-- User Id: 227171

create table purchases_items(

    purchaseID integer not null,

    itemID integer not null,

    primary key (purchaseID,itemID),

    foreign key (purchaseID) references purchases(purchaseID),

    foreign key (itemID) references item (itemID)

• Expert table

Code

-- User Id: 227171

create table expert(

    expertID integer primary key,

    firstName varchar(50) not null,

    lastName varchar(50) not null,

    type varchar(15) not null,

    email varchar(50) not null,

    phone varchar(15) not null,

    address varchar(100) not null,

    check (type in ('repairer','restorer','valuer'))

• Item_actions table

Code

-- User Id: 227171

create table item_actions(

    actionID integer primary key,

    itemID integer not null,

    dateDone date not null,

    expertID integer not null,

    cost decimal(10,2) not null,

    type varchar(15) not null,

    foreign key (itemID) references item (itemID),

    foreign key (expertID) references expert (expertID)

• Lease table

Code

-- User Id: 227171

create table lease(

    leaseID integer primary key,

    itemID integer not null,

    leaseDate date not null,

    period integer not null,

    client varchar(50) not null,

    cost decimal (10,2) not null,

    foreign key (itemID) references item (itemID)

• Staff table

Code

-- User Id: 227171

create table staff(

    staffID integer primary key,

    firstname varchar(50) not null,

    lastname varchar(50) not null,

    email varchar(50) not null,

    phone varchar(50) not null,

    position varchar(50) not null

• Expenses table

Code

-- User Id: 227171

create table expenses(

    expenseID integer primary key,

    type varchar(25) not null,

    amount decimal(10,2) not null,

    expenseDate date not null,

    staffID integer not null,

    foreign key (staffID) references staff (staffID)

• Collection table

Code

-- User Id: 227171

create table collection(

    collectionID integer primary key,

    itemID integer not null,

    dateRetained date not null,

    staffID integer not null,

    foreign key (staffID) references staff (staffID),

    foreign key (itemID) references item (itemID)

• Payment table

Code

-- User Id: 227171

create table payment(

    paymentID integer primary key,

    expenseID integer not null,

    paymentMethod varchar(30) not null default 'cash',

    foreign key (expenseID) references expenses (expenseID)

Question 4

Populating the tables with fictional data.

• Table collection

Code

INSERT INTO `antiques`.`category` (`categoryID`, `name`) VALUES ('1', 'Militaria'), ('2', 'Furniture'), ('3', 'Clothing'), ('4', 'clocks & watches'), ('5', 'Optical'), ('6', 'electrical'), ('7', 'sporting'), ('8', 'motors'), ('9', 'assorted');

Code

INSERT INTO `antiques`.`item` (`itemID`, `categoryID`, `name`, `costPrice`, `sellingPrice`, `description`) VALUES ('1', '1', 'ak 47', '120', '150', 'ak 47 ancient world war 2 riffle'), ('2', '2', '1 seater chair', '50', '80', 'a good relaxation chair'), ('3', '3', 'Cap', '15', '20', 'a good ancient chinese cap'), ('4', '4', 'Rolex watch', '200', '250', 'rolex watch'), ('5', '4', 'Rolex wall clock', '150', '180', 'A good old rolex wall clock'), ('6', '6', 'optical telescope', '80', '90', 'a good old stelescope'), ('7', '7', 'cooker', '123', '154', 'ancient cooker with oven'), ('8', '8', 'football', '50', '70', 'Lionel Messis first football'), ('9', '8', 'bm3 320 i gt', '3000', '4000', 'buy and drive old school BMW'), ('10', '9', 'sculpture', '300', '350', 'ancient rome sculpture');

Code

INSERT INTO `antiques`.`seller` (`sellerID`, `firstName`, `lastname`, `phoneNo`, `email`, `type`) VALUES ('1', 'Peter', 'Griffin', '+2332332432', '[email protected]', 'individual'), ('2', 'Lois', 'Griffin', '+2343243242', '[email protected]', 'individual'), ('3', 'Stewie', 'Griffin', '+32432423', '[email protected]', 'individual'), ('4', 'Brian ', 'Girffiin', '+223423423', '[email protected]', 'individual'), ('5', 'Meg', 'Griffin', '+32423232', '[email protected]', 'individual'), ('6', 'Cleveland', 'Brown', '+23211312', '[email protected]', 'individual'), ('7', 'Cleveland', 'Jr', '+24342323', '[email protected]', 'individual'), ('8', 'Rallo', 'tabbs', '+43423423', '[email protected]', 'individual'), ('9', 'ROberta', 'tabbs', '+3243423423', '[email protected]', 'indivual'), ('10', 'labada', 'tabbs', '+234234232', '[email protected]', 'individual');

• Buyer table

Code

INSERT INTO `antiques`.`buyer` (`buyerID`, `firstName`, `lastname`, `phoneNo`, `email`) VALUES ('1', 'Lebron', 'James', '+344342232', '[email protected]'), ('2', 'Brandon', 'Ingram', '+43434423', '[email protected]'), ('3', 'Rajon', 'Rondo', '[email protected]', '+3444343434'), ('4', 'Lonzo', 'Ball', '+343434343', '[email protected]'), ('5', 'Lance ', 'steph', '+34343434', '[email protected]'), ('6', 'Kyle', 'Kuzma', '+34343434', '[email protected]'), ('7', 'Josh', 'Hart', '+43434232332', '[email protected]'), ('8', 'Chris', 'Paul', '+334343243', '[email protected]'), ('9', 'Carmello', 'ANthony', '+34432434', '[email protected]'), ('10', 'James', 'Harden', '+43434234', '[email protected]');

• Sales table

Code

INSERT INTO `antiques`.`sales` (`saleID`, `saleDate`, `buyerID`) VALUES ('1', '2018-10-09', '1'), ('2', '2018-10-10', '1'), ('3', '2018-10-10', '3'), ('4', '2018-10-10', '4'), ('5', '2018-10-10', '5'), ('6', '2018-10-10', '6'), ('7', '2018-10-10', '8'), ('8', '2018-10-10', '8'), ('9', '2018-10-10', '9'), ('10', '2018-10-10', '10');

• Sales_items table

Code

INSERT INTO `antiques`.`sales_items` (`saleID`, `itemID`) VALUES ('1', '1'), ('1', '2'), ('2', '3'), ('3', '3'), ('4', '5'), ('6', '7'), ('7', '8'), ('9', '9'), ('9', '10'), ('10', '10');

• Purchases table

Code

INSERT INTO `antiques`.`purchases` (`purchaseID`, `purchaseDate`, `sellerID`) VALUES ('1', '2018-10-27', '1'), ('2', '2018-10-31', '2'), ('3', '2018-10-31', '3'), ('4', '2018-10-31', '4'), ('5', '2018-10-31', '5'), ('6', '2018-10-31', '6'), ('7', '2018-10-31', '7'), ('8', '2018-10-31', '8'), ('9', '2018-10-31', '10'), ('10', '2018-10-31', '10');

• Purchases_items

Code

INSERT INTO `antiques`.`purchases_items` (`purchaseID`, `itemID`) VALUES ('1', '1'), ('2', '2'), ('3', '3'), ('4', '4'), ('5', '5'), ('6', '6'), ('7', '7'), ('8', '8'), ('9', '9'), ('10', '10');

• Epxerts table

Code

INSERT INTO `antiques`.`expert` (`expertID`, `firstName`, `lastName`, `type`, `email`, `phone`, `address`) VALUES ('1', 'Kyrie ', 'Irving', 'valuer', '[email protected]', '+343423423', 'cletics td garden'), ('2', 'terry', 'Rozier', 'valuer', '[email protected]', '+324234234', 'td garden, celtics'), ('3', 'Jaylen', 'Brown', 'valuer', '[email protected]', '+223423434', 'td cgarden, celtics'), ('4', 'Gordon', 'Hayward', 'restorer', '[email protected]', '+3244342', 'td garden celtics'), ('5', 'Al', 'Hroford', 'restorer', '[email protected]', '+3223232', 'td garden,celtics'), ('6', 'Marcus', 'Smart', 'restorer', '[email protected]', '+32234234', 'td garden, celtics'), ('7', 'Markeif', 'Morris', 'repairer', '[email protected]', '+32342342', 'td  garden celtics'), ('8', 'Jayson', 'Tatum', 'repairer', '[email protected]', '+23312321', 'td garden celtics'), ('9', 'Jr', 'smith', 'repairer', '[email protected]', '+2332343', 'quick loans arena'), ('10', 'kevin', 'Love', 'repairer', '[email protected]', '+334334', 'quick loans arena');

Code

INSERT INTO `antiques`.`lease` (`leaseID`, `itemID`, `leaseDate`, `period`, `client`, `cost`) VALUES ('1', '1', '2018-10-17', '2', 'Marvel', '300'), ('2', '2', '2018-10-18', '3', 'Warner Bros', '400'), ('3', '3', '2018-10-18', '3', 'Warner Bros', '500'), ('4', '4', '2018-10-18', '4', 'Netflix', '434'), ('5', '5', '2018-10-18', '4', 'universal', '432'), ('6', '6', '2018-10-18', '4', 'universal', '3434'), ('7', '7', '2018-10-18', '4', 'Columbia ', '323'), ('8', '8', '2018-10-18', '4', 'Sony', '3422'), ('9', '9', '2018-10-18', '2', 'Sony', '342'), ('10', '10', '2018-10-18', '2', 'Warner Bros', '2432');

• Staff table

Code

INSERT INTO `antiques`.`staff` (`staffID`, `firstname`, `lastname`, `email`, `phone`, `position`) VALUES ('1', 'Steph', 'curry', '[email protected]', '+33434323', 'manager'), ('2', 'Demarcus', 'cousins', '[email protected]', '+3343423', 'assisttant manager'), ('3', 'Klay', 'Thompson', '[email protected]', '[email protected]', 'salesperson'), ('4', 'kevin', 'Durant', '[email protected]', '+43434334', 'salesperson'), ('5', 'Jon', 'smith', '[email protected]', '+4343434', 'salesperson'), ('6', 'Mike', 'Looney', '[email protected]', '+4343432', 'field agent'), ('7', 'Danerys', 'Teagryn', 'khaleesi', '[email protected]', 'field agent'), ('8', 'khal', 'drogo', '[email protected]', '+4343423', 'salesperson'), ('9', 'Brianner', 'Tarth', '[email protected]', '+324234324', 'salesperson'), ('10', 'Jon', 'White', '[email protected]', '+4343434', 'salesperson');

Code

INSERT INTO `antiques`.`item_actions` (`actionID`, `itemID`, `dateDone`, `expertID`, `cost`, `type`) VALUES ('1', '1', '2018-10-09', '1', '322', 'valuation'), ('2', '2', '2018-10-26', '3', '232', 'valuation'), ('3', '3', '2018-10-26', '3', '323', 'valuation'), ('4', '4', '2018-10-26', '4', '32', 'repair'), ('5', '5', '2018-10-26', '5', '232', 'repair'), ('6', '6', '2018-10-26', '6', '233', 'repair'), ('7', '7', '2018-10-26', '7', '232', 'restoration'), ('8', '8', '2018-10-26', '8', '32', 'restoration'), ('9', '9', '2018-10-26', '9', '232', 'restoration'), ('10', '10', '2018-10-26', '10', '223', 'restoration');

• Colection table

Code

INSERT INTO `antiques`.`collection` (`collectionID`, `itemID`, `dateRetained`, `staffID`) VALUES ('1', '1', '2018-10-23', '1'), ('2', '2', '2018-10-17', '2'), ('3', '3', '2018-10-17', '3'), ('4', '4', '2018-10-17', '4'), ('5', '5', '2018-10-17', '5'), ('6', '6', '2018-10-17', '6'), ('7', '7', '2018-10-17', '7'), ('8', '8', '2018-10-17', '8'), ('9', '9', '2018-10-17', '9'), ('10', '10', '2018-10-17', '10')

• Expenses table

Code

INSERT INTO `antiques`.`expenses` (`expenseID`, `type`, `amount`, `expenseDate`, `staffID`) VALUES ('1', 'accomodation', '233', '2018-10-09', '1'), ('2', 'accomodation', '2324', '2018-10-09', '2'), ('3', 'fuel', '3223', '2018-10-09', '3'), ('4', 'fuel', '2323', '2018-10-09', '4'), ('5', 'acommodation', '2324', '2018-10-09', '5'), ('6', 'accommodation', '323', '2018-10-09', '6'), ('7', 'accommodation', '23443', '2018-10-09', '8'), ('8', 'accommodation', '432', '2018-10-09', '8'), ('9', 'acommodation', '3232', '2018-10-09', '9'), ('10', 'fuel', '334', '2018-10-09', '10');

• Payment table

Code

INSERT INTO `antiques`.`payment` (`paymentID`, `expenseID`, `paymentMethod`) VALUES ('1', '1', 'cash'), ('2', '2', 'cash'), ('3', '3', 'cash'), ('4', '4', 'cash'), ('5', '5', 'cash'), ('6', '6', 'cash'), ('7', '7', 'cash'), ('8', '8', 'cash'), ('9', '9', 'cash'), ('10', '10', 'cash');          

Question 5

Queries

1.

Code

-- User Id: 227171

select * from item_actions where type='valuation';

2.

Code

-- User Id: 227171

select * from sales where saledate<curdate();

3.

Code

-- User Id: 227171

select paymentMethod,count(paymentID) from payment;

4.

Code

-- User Id: 227171

select sum(amount) from expenses;

5.

Code

-- User Id: 227171

select firstname,lastname,expensedate from expenses inner join staff on staff.staffID=expenses.staffID ;

6.

Code

-- User Id: 227171

select * from item_actions group by type;

7.

Code

-- User Id: 227171

select * from staff where staffID in (select staffID from expenses);

Code

-- User Id: 227171

select * from staff where staffID not in (select staffID from expenses);

9.

Code

-- User Id: 227171

select min(amount) from expenses;

10.

Code

-- User Id: 227171

select * from expenses having max(amount);