Database Schema and ERD for Item Insurance System

The provided assignment content describes the design of a database for an e-commerce application that allows users to store and insure their items. The database consists of five tables: User, All_Item_Details, Insurance_Company, Item_Insurance_Details, and Item_Picture. Each table has specific attributes and constraints, such as primary keys, foreign keys, and data types. The ERD (Entity-Relationship Diagram) shows the relationships between the tables, including one-to-one, one-to-many, and optional relationships.

Project Plan
Maya’s Home Inventory
Presented by Maya Rai
ITD 256 (E80W)
Oct. 20, 2016

1
Contents
Logical Model.............................................................................................................................................2
Project design and Scope.........................................................................................................................2
Introduction:........................................................................................................................................2
Purposes of Plan:.................................................................................................................................2
Goal and Objective of Plan:.................................................................................................................2
List of Entities with attributes..................................................................................................................3
ERD.........................................................................................................................................................6
Description of ERD.............................................................................................................................7
Five Business Rules..................................................................................................................................7
Physical Design............................................................................................................................................8
All Naming Convention Details................................................................................................................8
Database Schema....................................................................................................................................8

2
Logical Model
Project design and Scope
Introduction:
I am making a Home Inventory List to document my home assets for insurance purpose
and tracking my household expenses. My home Inventory project focuses on project’s purposes
and objectives. Keeping a Home inventory is very important to us because it helps to take
advantage of price discounts and to acknowledge the market conditions.
Purposes of Plan:
 Tracking the household expenses.
 Keep store’s information for returns and other purposes.
 To obtain and estimate household values for insurance purposes.
 To keep product information in case of lost or damages.
Goal and Objective of Plan:
 Keeps high levels of data security.
 Helps to keep reliable sources.
 Accomplish project business goals and objectives within defined budget and
time parameters.
 Helps to analyze about future market.

3
List of Entities with attributes
Entity One: (User)
User: In this table all authorized person’s login id and password and their type is stored. These
only persons can login to system and view the items details or do more processing according to
their right. This feature increases the security of system. Non-authorized person cannot access
the system information.
Attributes of User Entity:
1. userLoginID: this id is unique id, every user has its own id to access the system.
2. userLoginPassword: this is the login password used by the user to login to system.
3. userType: this is the type of user either user is normal or admin. This user type defines
the user privileges over the system.
Entity two: (All_Item_Details)
All_Item_Details: in this table all type of home items details will store.
Attributes:
ItemID: this id is unique id. This id will be autoincremented.
ItemName: this is the item name.
ItemSerialNumber: this is the item serial number which is unique for each item. This serial
number can be null for some items.
Description: this is the description field. In this field we can describe the item details.
itemCategory: this field defines in what category item fall like it is vehicle or electronic or
furniture.
itemQuantity: this field defines the item quantity, how many number of items of same name and
same type are there?.
itemCondition: this defines the item condition like good, bad or average or any other
description.

4
itemConditionDescription: this is the additional field to describe item condition in better or
descriptive way.
itemPurchaseDate: this field defines the purchase date of item.
itemConpanyName: this field defines the company name from where item is purchased.
itemPurchaseLocation: this defines the item purchase location.
itemPurchseDetails: this is the additional field to describe the item purchase details.
itemManufactureDate: this field defines the item manufacture details
itemManufactureDetails: this is the additional field to describe the item manufacture details.
itemPurchasePrice: this is the item purchase cost.
itemCurrentValue: this is the item value in the current time.
itemWarrentyInformation: this field defines the item warranty description if any.
itemWarrentyLastDate: this field defines the item warranty last date.
Entity Three: (Item_Picture)
Item_Picture: this table stores all the pictures for all items. There may be zero or more item
pictures.
Attributes:
ItemID: this is the item id. Item id could be duplicate but cannot be null.
ItemPicFileName: this field store the item picture.

5
Entity Four: (Insurance_Company)
Insurance_Company: this table stores the insurance company details like what is the company
name , location and any other description if there any.
Attributes: (companyID, name, location, description)
Entity Five: (Item_Insurance_Details)
Item_Insurance_Details: this tables stores the insurance details for each item.
Attributes: (itemID, companyID,claimInsurance, insuranceDescription, insuranceStartDate,
insuranceLastDate, insuranceFinishDate, premiumAmont, totalInsuranceAmount)

6
ERD

7
Description of ERD
Relation between:
All_Item_Details and Item_pictures: one optional to many optional.
There may be one item has one or more images or may not.
All_Item_Details and Item_Insurance_Details: One to one relation.
One item must have one insurance details in Item_Insurance_Details.
Insurance_Company and Item_Insurance_Details: One to Many
There may be one item or more item insurance with one company.
Five Business Rules
1. The stored items details in database should be unique by their id. All items serial number
should be unique. The serial number would be null for some items.
2. There should be unique user’s login ids in user tables.
3. All insurance company details should be unique.
4. User could store the item images if there is any image available for any items.
5. User can insure their items with different insurance companies and all these details
should be well stored in database without any duplicacy.
6. Item details should be stored as so that we can retrieve the items details according to their
type in database.

8
Physical Design
All Naming Convention Details
1. All table names are declared using underscore. If table name has more than one word
then they are separated with underscore.
2. All attributes names are declared using camel style.
Database Schema
Table: User
ColumnName Datatype Contrainsts
loginID INT PRIMARY KEY
AUTO_INCREMENT
password VARCHAR(50) NOT NULL
userType ENUM(‘NORMAL’ ,
‘ADMIN’)
NOT NULL
Table: All_Item_Details
ColumnName Datatype Contrainsts
ItemID INT PRIMARY KEY
AUTO_INCREMENT
ItemName VARCHAR(30) NOT NULL
ItemSerialNumber VARCHAR(40) DEFAULT NULL
Description VARCHAR(100) DEFAULT NULL
itemCategory VARCHAR(40) NOT NULL
itemCondition ENUM(‘GOOD’,’BAD’,’AVG’
)
NOT NULL
itemConditionDescription VARCHAR(100) DEFAULT NULL
itemPurchaseDate DATE NOT NULL
itemCompanyName VARCHAR(50) NOT NULL
itemPurchaseLocation VARCHAR(40) NOT NULL

9
itemPurchaseDetails VARCHAR(100) DEFAULT NULL
itemManufactureDate DATE NOT NULL
itemManufactureDetails VARCHAR(100) DEFAULT NULL
itemPurchasePrice DOUBLE NOT NULL
itemCurrentValue DOUBLE DEFAULT NULL
itemWarrentyInformation VARCHAR(100) DEFAULT NULL
itemWarrentyLastDate DATE DEFAULT NULL
Table: Insurance_Company
ColumnName Datatype Contrainsts
companyID INT PRIMARY KEY
Name VARCHAR(40) NOT NULL
Location VARCHAR(40) NOT NULL
description VARCHAR(100) DEFAULT NULL
Table: Item_Insurance_Details
ColumnName Datatype Contrainsts
itemID INT FOREIGN KEY (itemId)
REFERENCES
All_Item_Details (itemId)
companyID INT FOREIGN KEY (companyId)
REFERENCES
Insurance_Company
(comapanyId)
claimInsurance VARCHAR(40) NOT NULL
insuranceDescription VARCHAR(100) DEFAULT NULL
insuranceStartDate DATE NOT NULL
insuranceLastDate DATE NOT NULL
insuranceFinishDate DATE DEFAULT NULL
premiumAmount DOUBLE NOT NULL
totalInsuranceAmount DOUBLE DEFAULT NULL

10
Table: Item_Picture
ColumnName Datatype Contrainsts
itemID INT FOREIGN KEY (itemId)
REFERENCES
All_Item_Details (itemId)
itemPicFileName BLOB DEFAULT NULL