University Database Assignment: Home Inventory Database Design and SQL

This assignment presents a home inventory database implemented using MySQL Query Browser. The solution includes the creation of database tables such as USERS, INSURANCE_COMPANY, ALL_ITEM_DETAILS, ITEM_PICTURES, and ITEM_INSURANCE_DETAILS. SQL commands are provided for creating the database, defining table structures, and inserting data. Select statements are used to display data, including queries to filter based on conditions and join data from multiple tables. Security measures involve a separate USERS table with login credentials and user types (ADMIN/NORMAL) to control access and permissions. The strategy for testing, collecting feedback, and making future updates includes designing the database to avoid anomalies and accommodate future requirements. The approach for validating effectiveness focuses on data integrity, such as preventing duplicate item pictures and ensuring unique item IDs to eliminate data redundancy, along with the ability to store multiple images for a single item.

[HOME INVENTORY DATABASE]
Student Name:
Student ID:
Course Name:
Course ID:
Faculty Name:
University Name:

1
Contents
1. Software Tool Description...........................................................................................................2
3.SQL Section..................................................................................................................................2
4. Security measures you will put in place for the database............................................................2
5. Your strategy for testing, collecting feedback and making future updates.................................2
6. Your strategy for validating effectiveness at meeting the original requirement(s). Identify
specific metrics you will collect and analyze..................................................................................2

2
1. Software Tool Description
I have used Mysql Query Browser 5.0. In this software it is easy to create new script, can copy
one script data to another script, can save as with other name.
In this it is easy to edit the script and the queries. This software is user friendly we can do many
things with queries and by keyboard shortcut commands like to delete the databases by ctl +
delete or with Mysql query. We can import and export mysql databases using command or with
file menu.
3. SQL Section
a) Create Commands
CREATE DATABASE HOME_INVENTORY;
USE HOME_INVENTORY;
CREATE TABLE USERS(
loginID VARCHAR(30) PRIMARY KEY,
pass VARCHAR(50) NOT NULL,
userType ENUM('ADMIN','NORMAL')
);

3
CREATE TABLE INSURANCE_COMPANY(
compnayID INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
location VARCHAR(50) NOT NULL,
description VARCHAR(100) DEFAULT NULL
);
CREATE TABLE ALL_ITEM_DETAILS(
itemID INT PRIMARY KEY,
itemName VARCHAR(60) NOT NULL,
itemSerialNumber INT UNIQUE NOT NULL,
description VARCHAR(100) DEFAULT NULL,
itemCategory VARCHAR(40),
itemQuantity INT NOT NULL,
itmeCondition VARCHAR(30),
itemConditionDescription VARCHAR(100),
itemPurchaseDate DATE,
itemConpanyName VARCHAR(80),
itemPurchaseLocation VARCHAR(40),
itemPurchaseDetails VARCHAR(100),
itemManufactureDate DATE,
itemManufactureDateDetails VARCHAR(80),
itemPurchasePrice DOUBLE,
itemcurrentValue DOUBLE,
itemWarrentyInformation VARCHAR(100),
itemWarrentyLastDate DATE

4
);
CREATE TABLE ITEM_PICTURES(
itemID INT REFERENCES ALL_ITEM_DETAILS(itemID),
itemPicture BLOB(100)
);
CREATE TABLE ITEM_INSURANCE_DETAILS(
itemID INT REFERENCES ALL_ITEM_DETAILS(itemID),
companyID INT REFERENCES INSURANCE_COMPANY(companyID),
claimInsurance VARCHAR(40),
insuranceDescription VARCHAR(100),
insuranceStartDate DATE,
insuranceLastDate DATE,
insuranceFinishDate DATE,
premiumAmount DOUBLE,
totalInsuranceAmount DOUBLE
);

5
b) Select statement to display all data
SELECT * FROM USERS;
SELECT * FROM INSURANCE_COMPANY;
SELECT * FROM ALL_ITEM_DETAILS;
SELECT * FROM ITEM_PICTURES;
SELECT * FROM ITEM_INSURANCE_DETAILS;
c) Select statement to display data meeting certain condition
Query: Display all item details which items was purchased before this date 2016/05/05.
SELECT * FROM ALL_ITEM_DETAILS WHERE itemPurchaseDate > '2016-05-05';
d) Select statement to display data from two tables
Query: Display item name, item current value and all item insurance details which were
insurance.
SELECT itemName, itemcurrentValue, iid.* FROM ALL_ITEM_DETAILS i INNER JOIN
ITEM_INSURANCE_DETAILS iid on iid.itemID = i.itemID;

6
4. Security Measures
For security measure I crated a separated table for user. This table stores the user login id and
password and the user type. User type can be admin or normal. An admin can insert, update, or
view the details. A normal user can only view the details. So there can be more than one admin
or more than one user who can access the database.
5. Your strategy for testing, collecting feedback and making future
updates
I have designed my database according to avoid any anomaly in database. In future this
database designed can be updated to meet new requirements.
6. Your strategy for validating effectiveness at meeting the original
requirement(s). Identify specific metrics you will collect and
analyze.
To store the item images I have created a separate table. According to this table design you can
store more than one image for a single item. But user can make mistake by inserting duplicate
value of item pictures so I have make this column value unique. If user insert same image more
than one time then he/she will get message, so this removes the data redundancy.
In ALL_ITEM_DETAILS table an item details are saved in brief. This is assumed that all the
item related information is stored in this table and each item will identified with unique id. This
id is integer type.