Project 1 provides experience with creating ERDs and writing CREATE TABLE statements for the Expense Reporting database at XCorp, a fast-growing web consulting company.

You will design an ER Diagram using ER Assistant based on the table information and relationships provided in this project. In addition, you will write complete CREATE TABLE statements including column definitions, referential integrity constraints, and CHECK CONSTRAINT clauses. After creating the tables, you will insert example data and perform a few basic queries. You must have DROP statements for all database objects. The order of the DROP statements must be consistent with the dependencies among the database objects.

CSIS 525 PROJECT 1 INSTRUCTIONS
Project 1 provides experience with creating ERDs and writing CREATE TABLE statements for
the Expense Reporting database at XCorp, a fast-growing web consulting company.
You will design an ER Diagram using ER Assistant based on the table information and
relationships provided in this project. In addition, you will write complete CREATE TABLE
statements including column definitions, referential integrity constraints, and CHECK
CONSTRAINT clauses. After creating the tables, you will insert example data and perform a few
basic queries. You must have DROP statements for all database objects. The order of the DROP
statements must be consistent with the dependencies among the database objects.
1. Table Descriptions
The Expense Reporting database contains tables to track users and expense reports (heading and
detail data) along with support tables to track expense categories, status codes, and limits on
expense category spending. Table 1 briefly summarizes the meaning of each table. Tables 2–7
describe the columns in each table. Use the table and column names as shown in Tables 1–7.
Choose the appropriate data type for each column.
Table 1: Tables in the Expense Reports Database
Table Name Description Users Contains data about users who can submit
expense reports ExpenseReport Contains data on the headings of expense reports
ExpenseItem Contains data on the detail lines of expense
reports ExpCat Contains data about expense categories in which
expense items are associated Asset Contains data about company assets that might
be associated with expense items OrgUnit Contains data about the organizational units in
which users are members BudgetItem Contains data about expense budgets by
organizational unit and expense category Page 1 of 6 CSIS 525 Table 2: Columns in the Users Table
Column Name Data Type Comments UserNo Positive whole number Primary key (should be system generated) UserFirstName Variable length string Max of 50 characters; nulls not allowed UserLastName Variable length string Max of 50 characters; nulls not allowed UserPhone Variable length string Max of 20 characters; nulls allowed UserEmail Variable length character
string Max of 50 characters; unique; nulls not allowed UserOrgNo Positive whole number Foreign Key to the OrgUnit table; nulls not
allowed Table 3: Columns in the ExpenseReport Table
Column Data Type Comments ERNo Positive whole
number Primary key (should be system generated) ERDesc Variable length string Max of 255 characters; nulls not allowed ERSubmitDate Date/Time Date and time submitted; default should be the
current date and time (use a function to
generate the default value); nulls not allowed ERStatusDate Date/Time Date and time when the status changed; defaults
to the current date and time when a record is
inserted; changes when the expense report is
approved or denied; use a function to generate
the default value; nulls not allowed ERStatus Variable length string Default value is “PENDING” meaning that the
expense report is waiting for approval; this field
should be limited to one of the following values
“PENDING”, “APPROVED”, or “DENIED”;
nulls not allowed SubmitUserNo Positive whole
number Foreign key to the Users table; identifies the
user who submitted the expense report; nulls
not allowed ApprUserNo Positive whole
number Foreign key to the Users table; identifies the
user who approves the expense report; nulls
allowed for the case of “PENDING” reports
only Page 2 of 6 CSIS 525 Table 4: Columns in the ExpenseItem Table
Column Data Type Comments EINo Positive whole
number Primary key (should be system generated) ExpDesc Variable length string Max of 255 characters; nulls not allowed ExpenseDate Date/Time Date and time that the expense was incurred;
default should be the current date and time (use
a function to generate the default value) ExpAmt Dollar amount Amount of the expense; nulls not allowed;
default is 0 (zero) ExpApprAmt Dollar amount Approved amount of the expense; default is 0
(zero) ERNo Positive whole
number Foreign key to the ExpenseReport table;
identifies the expense report that contains the
item; nulls not allowed ECNo Positive whole
number Foreign key to the ExpCat table; nulls not
allowed AssetNo Positive whole
number Foreign key to the Asset table; Nulls allowed Table 5: Columns in the ExpCat Table
Column Data Type Comments ECNo Positive whole number Primary key (should be system generated) ECName Variable length string Max of 255 characters; nulls not allowed ECLimit Amount allowed per expense category expanse
item; default is 0 (zero); nulls not allowed Dollar Amount Table 6: Columns in the Asset Table Page 3 of 6 CSIS 525 Column Data Type AssetNo Positive whole number Primary key (should be system generated) AssetDesc Variable length string Comments
Max of 255 characters; nulls not allowed Table 7: Columns in the OrgUnit Table
Column Data Type Comments OrgNo Positive whole
number Primary key (should be system generated) OrgName Variable length string Maximum length of 50 characters; nulls not
allowed OrgParentNo Positive whole
number Refers to OrgNo; nulls allowed for the case of a
parent organizational unit Table 8: Columns in the BudgetItem Table
Column Data Type Comments BINo Positive whole number Primary key (should be system generated) BIYear Number Must be greater than or equal to 1900; default
should be 2005; nulls not allowed BIAmt Dollar Amount The budgeted amount for the year; nulls not
allowed; default is 0 (zero) BIActual Dollar Amount An ongoing sum of the actual expenses
approved for the category/org unit combination
in the year; default is 0 (zero) OrgNo Positive Whole
Number Foreign Key to the OrgUnit table; nulls not
allowed ECNo Positive whole number Foreign Key to the ExpCat table; nulls not
allowed 2. Referential Integrity Constraints
Define referential integrity constraints as defined in Tables 2–7. Enforce each referential integrity
constraint. For each referential integrity constraint, use a constraint name in the CREATE
TABLE statement.
Choose appropriate actions when referenced rows are deleted. When an expense report is
deleted, both the heading row in the ExpenseReport table and the associated detail rows in the
ExpenseItem table must be deleted. For other referential integrity constraints involving required
foreign keys (nulls not allowed), deleting a referenced row in a parent table must not be
permitted if there are associated referencing rows in a child table. For referential integrity
constraints involving optional foreign keys (nulls allowed) except ExpenseReport.ApprUserNo,
Page 4 of 6 CSIS 525 deleting a referenced row must make the foreign key of the referencing row null. For
ExpenseReport.ApprUserNo, restrict deletion of related User rows. Page 5 of 6 CSIS 525 3. Check Constraints
Write named constraints using the CHECK CONSTRAINT clause for the following integrity
constraints: Email contains an @ ERStatusDate >= ERSubmitDate BIYear>=1900 ERStatus is ‘PENDING’ ‘APPROVED’, or ‘DENIED’ ExpApprAmt <= ExpAmt The combination of BIYear, OrgNo, and ECNo is unique in the BudgetItem table
Nulls are allowed for ExpenseReport.ApprUserNo only if ERStatus is equal to
PENDING
4. Load Data
Use the text file containing INSERT statements to load the data in the tables. The text file is
located in Blackboard. Note that the INSERT statements use the table and column names listed
in Tables 1–7.
5. Deliverables
The following items must be uploaded to Blackboard upon completion of Project 1: Screenshot of the ER Diagram while it is displayed in ER Assistant. Paste this into a
Microsoft Word document. Make sure that the ERD fits on a single sheet and is
legible. Screenshots of the CREATE TABLE statements that include the appropriate
constraints. Screenshots of the INSERT statements. Screenshots of the SQL statements and the corresponding results.
Submit this assignment by 11:59 p.m. (ET) on Sunday of Module/Week 4. Page 6 of 6

• 1493178375-Project_1_Instructions2 1.doc

ComputerScienceExpert

(11)

Status NEW Posted 26 Apr 2017 03:04 AM My Price 9.00

-----------

Attachments

file 1493178378-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer