Database Design and Implementation: Expense Reporting Tables Project

This assignment presents a comprehensive database project focused on developing an expense reporting system for XCorp, a web consulting company. The project involves designing an ER Diagram, creating SQL CREATE TABLE statements with appropriate data types, primary and foreign key constraints, and CHECK constraints to ensure data integrity. The solution includes the creation of tables such as 'asset', 'budgetitem', 'expcat', 'expenseitem', 'expensereport', 'orgunit', and 'users'. Furthermore, the assignment covers table population with sample data and the execution of basic SQL SELECT statements to display table content. The solution also includes the implementation of various constraints, such as email format validation, date comparisons, and unique constraints. This project provides practical experience in database design, SQL implementation, and data integrity management.

Running head: DATABASE DEVELOPMENT AND DESIGN
DATABASE DEVELOPMENT AND DESIGN
Name of the Student:
Name of the University:
Author note

1
DATABASE DEVELOPMENT AND DESIGN
ER Diagram
Table creation
SQL statement for creation
CREATE TABLE `asset` (
`AssetNo` int(11) NOT NULL,
`AssetDesc` varchar(255) NOT NULL
);

2
DATABASE DEVELOPMENT AND DESIGN
CREATE TABLE `budgetitem` (
`BINo` int(11) NOT NULL,
`BIYear` int(11) NOT NULL,
`OrgNo` int(11) NOT NULL,
`ECNo` int(11) NOT NULL,
`BIAmt` float NOT NULL,
`BIActual` float NOT NULL
);
CREATE TABLE `expcat` (
`ECNo` int(11) NOT NULL,
`ECName` varchar(255) NOT NULL,
`ECLimit` float NOT NULL
) ;
CREATE TABLE `expenseitem` (
`EINo` int(11) NOT NULL,
`ExpDesc` varchar(255) NOT NULL,

3
DATABASE DEVELOPMENT AND DESIGN
`ExpenseDate` date NOT NULL,
`ExpAmt` float NOT NULL,
`ExpApprAmt` float NOT NULL,
`ERNo` int(11) NOT NULL,
`ECNo` int(11) NOT NULL,
`AssetNo` int(11) NULL
) ;
CREATE TABLE `expensereport` (
`ERNo` int(11) NOT NULL,
`ERDesc` varchar(255) NOT NULL,
`ERSubmitDate` date NOT NULL,
`ERStatusDate` date NOT NULL,
`ERStatus` varchar(50) NOT NULL,
`SubmitUserNo` int(11) NOT NULL,
`ApprUserNo` int(11) NOT NULL
) ;

4
DATABASE DEVELOPMENT AND DESIGN
CREATE TABLE `orgunit` (
`OrgNo` int(11) NOT NULL,
`OrgName` varchar(50) NOT NULL,
`OrgParentNo` int(11) DEFAULT NULL
) ;
CREATE TABLE `users` (
`UserNo` int(11) NOT NULL,
`UserFirstName` varchar(50) NOT NULL,
`UserLastName` varchar(50) NOT NULL,
`UserPhone` varchar(50) NOT NULL,
`UserEmail` varchar(50) NOT NULL,
`UserOrgNo` int(11) NOT NULL
) ;
SQL statements for Primary/foreign keys
ALTER TABLE `asset`
ADD PRIMARY KEY (`AssetNo`);

5
DATABASE DEVELOPMENT AND DESIGN
ALTER TABLE `budgetitem`
ADD PRIMARY KEY (`BINo`),
ADD KEY `OrgNo` (`OrgNo`),
ADD KEY `ECNo` (`ECNo`);
ALTER TABLE `expcat`
ADD PRIMARY KEY (`ECNo`);
ALTER TABLE `expenseitem`
ADD PRIMARY KEY (`EINo`),
ADD KEY `ECNo` (`ECNo`),
ADD KEY `ERNo` (`ERNo`),
ADD KEY `AssetNo` (`AssetNo`);
ALTER TABLE `expensereport`
ADD PRIMARY KEY (`ERNo`),
ADD KEY `ApprUserNo` (`ApprUserNo`),
ADD KEY `SubmitUserNo` (`SubmitUserNo`);
ALTER TABLE `orgunit`
ADD PRIMARY KEY (`OrgNo`),
ADD KEY `OrgParentNo` (`OrgParentNo`);

6
DATABASE DEVELOPMENT AND DESIGN
ALTER TABLE `users`
ADD PRIMARY KEY (`UserNo`),
ADD KEY `UserOrgNo` (`UserOrgNo`);
ALTER TABLE `budgetitem`
ADD CONSTRAINT `budgetitem_ibfk_1` FOREIGN KEY (`OrgNo`) REFERENCES
`orgunit` (`OrgNo`),
ADD CONSTRAINT `budgetitem_ibfk_2` FOREIGN KEY (`ECNo`) REFERENCES `expcat`
(`ECNo`);
ALTER TABLE `expenseitem`
ADD CONSTRAINT `expenseitem_ibfk_1` FOREIGN KEY (`ECNo`) REFERENCES
`expcat` (`ECNo`),
ADD CONSTRAINT `expenseitem_ibfk_2` FOREIGN KEY (`ERNo`) REFERENCES
`expensereport` (`ERNo`),
ADD CONSTRAINT `expenseitem_ibfk_3` FOREIGN KEY (`AssetNo`) REFERENCES
`asset` (`AssetNo`);
ALTER TABLE `expensereport`
ADD CONSTRAINT `expensereport_ibfk_1` FOREIGN KEY (`ApprUserNo`)
REFERENCES `users` (`UserNo`),
ADD CONSTRAINT `expensereport_ibfk_2` FOREIGN KEY (`SubmitUserNo`)
REFERENCES `users` (`UserNo`);

7
DATABASE DEVELOPMENT AND DESIGN
ALTER TABLE `orgunit`
ADD CONSTRAINT `orgunit_ibfk_1` FOREIGN KEY (`OrgParentNo`) REFERENCES
`orgunit` (`OrgNo`);
ALTER TABLE `users`
ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`UserOrgNo`) REFERENCES `orgunit`
(`OrgNo`);
Table Screenshots
Asset
Budget Item
ExpCat

8
DATABASE DEVELOPMENT AND DESIGN
ExpenseItem
ExpenseReport
OrgUnit

9
DATABASE DEVELOPMENT AND DESIGN
Users
Constraints
Email contains an @
ALTER Table users
ADD CONSTRAINT e_mail CHECK (UserEmail LIKE '%@%');
ERStatusDate >= ERSubmitDate
ALTER Table expensereport
ADD CONSTRAINT date_check CHECK (ERStatusDate >= ERSubmitDate);
BIYear>=1900
ALTER Table budgetitem

10
DATABASE DEVELOPMENT AND DESIGN
ADD CONSTRAINT year_check CHECK (BIYear >= 1900);
ERStatus is ‘PENDING’ ‘APPROVED’, or ‘DENIED’
ALTER Table expensereport
ADD CONSTRAINT status_check CHECK (ERStatus = 'PENDING' or ERStatus =
'APPROVED' or ERStatus = 'DENIED');
ExpApprAmt <= ExpAmt
ALTER Table expenseitem
ADD CONSTRAINT amount_check CHECK (ExpApprAmt <= ExpAmt);
The combination of BIYear, OrgNo, and ECNo is unique in the BudgetItem table.
ALTER TABLE `budgetitem` ADD UNIQUE(`ECNo`);
ALTER TABLE `budgetitem` ADD UNIQUE(`ECNo`);
ALTER TABLE `budgetitem` ADD UNIQUE(`ECNo`);

11
DATABASE DEVELOPMENT AND DESIGN
Nulls are allowed for ExpenseReport.ApprUserNo only if ERStatus is equal to PENDING
ALTER TABLE `expensereport` CHANGE `ApprUserNo` `ApprUserNo` INT(11) NULL;
Table Definitions
DESC asset;
DESC budgetitem;