Quiz 3 Nov 5, 2014

This will  be an in-class exam,  to take during our class period,  using your own paper.  I will grade and post those grades to Blackboard.

I will ask you to draw some conceptual schemas (  hand drawings are fine) and write some simple SQL code.

Also,  Number your answers, in order,  and write legibly. If I can”t read it I won”t grade it. Label and  Box your answers.

Find your section number and put it on your paper as well

 

Good luck..

 

Reference Tables for quiz questions:

Context Notes: A software distributor  wishes to keep track of  her Items, Invoices, and stock control of system softwares.  Below are a few data use case tables. She stocks the following software products and also wants to keep track of what invoices are issued to which customers,  and their purchases.

 

QuantityOnHand refers to boxed disk sets that may be  ordered.

Price is for a boxed set

 

Item  Data Use Case table

ItemCode	Title	Category	QuantityOnHand	Price(USD)
V1	BitKeeper	RCS	100	20.2
G2	Git	RCS	20	10.2
S1	SourceSafe	RCS	5	10.2
L1	Linux	OS	10	12
U2	Unix	OS	20	5
M1	MS	OS	5	15
K4	Scala	Lang	50	10

 

 

Customer Data Use Case table

CustomerNumber	Name	Location	AnnualSales
M10	MicroAge	Tempe	3.6
B20	BestBuy	Tempe	8.7
T20	Target	Mesa	6.2

 

Invoice

Data use case table

CustomerNr	InvoiceNr	DateIssued (ymd)	DatePaid (ymd)
B20	205	2014 ” 10 ” 23	2014 ” 11 ” 30
T20	132	2013-09-21	2013 ” 12 ” 23
B20	107	2013- 10 ” 13	12/25/14

 

 

LineItemInvoice

line item invoice

InvoiceNr	ItemCode	QtyOrdered	UnitPrice	Subtotal (USD)
205	U2	3	4	12
205	M1	2	10	20
107	M1	6	4	24

 

Note: Please orient your drawings on your paper as follows:

Place Customer graph in upperleft  quadrant,

Place Invoice graph  in upper right  quadrant

Place Item graph in lower right  quadrant

Remaining diagram components in lower left and quadrant.

 

Q1: Draw an ORM diagram for Customer.

 

Assume the following constraints: Every Customer must have  a unique name, which is a value object. Customers are identified by Customer number, a Character array of size 3. Note: This is a SQL data type denoted by : Char(3).  The Location is  mandatory,  is a value object and may be simply a string, Varchar(20). Annual sales is a Decimal( 5,2)  Note: this is a SQL data type that makes space for 5 digits, with 2 to the right of the decimal point. Annual sales are scaled by millions, for example, 3.6 means 3.6 million in dollar sales. ( You may leave this data scaled, as is).

 

 

Q#2 Draw an ORM diagram for Item.

Item code is unique. every Item must have a title, category, quantity on hand. price is optional

You will need an enumeration for the Categories, and so indicate that on the ORM diagram.

 

 

 

Q #3 Draw an ORM diagram for Invoice

Hint: remember to combine semantic domains.

 

Q #4 Draw an ORM diagram for LineItemInvoice. Note, this diagram should be connected to the previous diagrams. You will need a nested object. Note: UnitPrice is  optional while the other objects are required.

 

Q # 5

Create a database, ItemDB, and then create a table, call it  Item.  Insert just the first two rows of the data use case Item table.

Show the following:

1. Your table schema, denoting the key(s) by an underline, and any optional column in square brackets. ‘
2. Your SQL code that creates the table, does the insert and a query that shows all the column values.
3. Include a Check constraint on the Category column that checks that Category is one of the three shown.

 

 

**Hint***

From the Derby documentation, here is an example of the ‘Check” constraint.

Here the column name is Meal, with a one-character data type, Char(1)

Constraint is a key word and must be there.

Meal_Constraint is a user defined tag

Check is a key word

As you see, the Check is followed by a conditional that checks if the Meal character is one of the four shown.

Upon insertion or update, failure will result in an error and termination of the transaction.

 

 

MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT

    CHECK (MEAL IN (‘B”, ‘L”, ‘D”, ‘S”)),

Click here to have a similar paper done for you by one of our writers within the set deadline at a discounted