MTE.1

CSE4701

CSE4701 Midterm Exam Statistics (Spr15)

Worry a lot!

Notes:• Final Exam 120 points• MT - range from 40-50%; • FE - range from 60-50%• Track Performance from MT to Final• Exam Differential from MT to Final• If Increase - Weighted Average• If Stay Same - that is your Performance• If Decrease - that is your Performance

MTE.2

CSE4701

CSE4701 Homeworks Fa15

MTE.3

CSE4701

CSE4701 Grade Guesstimates (Spr15)

Notes:• All Subject to Change• Used 10%HW, 40%P, 50% • From Intro Overheads

Must Pass Both Projects And Exams to PASS!!!

MTE.4

CSE4701

Problem 1a - Relational Algebra (Spr15) List the name and address of all vendors that provide both hardware

and software.

BOTHV = HVendorID,SVendorID (

HWFlag=T and SWFlag=T (Vendor))

ANS = HVName, HVAddr (HardwareVendor * BOTHV)HVendorID

SVName, SVAddr (SardwareVendor * BOTHV)SVendorID

MTE.5

CSE4701

Problem 1b - Relational Algebra (Spr15) List the versions and vendor names of all C++

software installed on computers made by the vendor DEC.

COMPV = VendorID,CInventNum (Computer * Inventory)

CInventNum=InventNum

HWVEND = HVendorID,CInventNum (COMPV * Vendor)

VendorID

DEC = CInventNum ( HVName=DEC

(HWVEND * HardwareVendors))HVendorID

DECSW = SInventNum (DEC * InstalledSW)

CInventNum

DEC++SW = SVendorID,SWVersion ( SWName=C++

DECSW * Software)SInventNum

ANS = SWName,SWVersion (DEC++SW * SoftwareVendor)

SVendorID

MTE.6

CSE4701

Problem 1c - Relational Algebra (Spr15) List all purchase orders from the vendor Dell that

have been ordered but not delivered.

DELL = VendorID ( HVName=DELL

(Vendor * HardwareVendors))HVendorID

ANS = PONumber, PODate, POCost ( DeliveredDate=Null

DELL * Inventory)

VendorID

MTE.7

CSE4701

Problem 2 - ER Specialization (Spr15) Each Specialization was worth 5 pts – deductions for

Not indicating Total Not supplying attributes

Most Popular Answers: Disjoint: Inventory with Computer, Accessory, and

Software as Children Overlap Vendor with HWVendor and SWVendor as

Children Union:

DeployedPC

Computer

Accessory

Software

u

1

1

m

m

MTE.8

CSE4701

Problem 3- Functional Dependencies (Spr15)

CInventNum ComputerName, ComputerType

CInventNum, AccID ComputerName, ComputerType

SVendorID SVName, SVAddr

SVendorID, SVName, SWName, SWVersion SWDesc, SWAddr

SVendorID SWName (first two basically equivalent)

SVName SWName

SWName SWVersion

Computer( CInventNum, ComputerName, ComputerType, AccID);

Inventory( InvenNum, SerialNum, PONum, PODate, DeliveredDate, POCost, VendorID);

SoftwareVendor (SVendorID, SVName, SVAddr, SWName, SWVersion, SWDesc);

PONum InventNum, SerialNum, PODate, DeliveredDate, etc.

InventoryNum SerialNum (or some other one not involving PO)

MTE.9

CSE4701

Prob 4 – Relational Schema Analysis (Spr15)

Insert - For a new computer, you must always insert an accessory (since it is part of the key). If there are N accessories, there are N rows for each computer.Update – if you change the Name or Type, you must change all N tuples.Delete - No obvious delete anomalies.Conclusion: Computer represents two different entities (Guideline 1) – the computer and its accessories, and as a result, violates Guideline 2 in regards to insert anomalies. A better design would separate accessories in a similar manner to the Installed Software table.

Computer( CInventNum, ComputerName, ComputerType, AccID);

Accessory( AccID, AInventNum, HVendorID, AccName, AccType, AccSize);

From an inventory control perspective, there is no way to track the total number of each accessory that has been purchased. You may have 10 USB 120 Gig external hard drives, and each one would have its own AccID and AIventNum. The other problem is related to Guideline 3 due to null values for AccSize (limited problem).Insert, Delete, and Update: No obvious anomalies. Conclusion: The table is OK – but it could be improved by separating out the different types of accessories (that have been purchased). It may also make sense not to track this at all in their gory detail – many companies (UConn included) don’t track equipment that is less than $1000, and many of these fit into that category.

MTE.10

CSE4701

Prob 4 – Relational Schema Analysis (Spr15)

The only real problem in this table is that SVendorID, SWName, and SWVersion are foreign keys into the SoftwareVentor table, and as a result, this information is replicated in both tables. Conclusion: There may be a better way to design the Software, Installed Software, and SoftwareVendor tables, particularly in regards to reducing the key size (and hence the foreign key linkages).

Software( SInventNum, SVendorID, SWName, SWVersion);

Inventory(InvenNum, SerialNum, PONum, PODate, DeliveredDate, POCost, VendorID);

This table suffers violates two guidelines: Guideline 1 in regards to representing two different entities (inventory and purchase orders), and Guideline 3 in regard to an excessive amount of null values. Insert, Delete, and Update: No obvious anomalies. Conclusion: Split into two different tables: Inventory (InvenNum, SerialNum, PONum) and PurchaseOrder (PONum, PODate, DeliveredDate, POCost, VendorID) which will address Guideline 1 and will not result in a Inventory tuple until the item is actually received. DeliveredDate will still be null for all outstanding orders.

MTE.11

CSE4701

Prob 4 – Relational Schema Analysis (Spr15)

InstalledSoftware is dealing with two foreign key references to the Computer and Software tables, respectively. Vendor is allowing us to unify the different ID tracking systems for software and hardware vendors. The only problem with Vendor is that there are potentially null values for companies that sell either hardware or software but not both. You could argue that the flags are not needed in Vendor as well, since the null values (or not-null) has this information.Conclusion: In the case of Vendor (and VendorID, SVendorID, HVendorID), this may be a poor design and if the database has not been deployed, it may make senseto totally redesign this identifier to have a single identifier. This would allow the Vendor table to be eliminated. This would separate vendor common information into a single Vendor (VendorID, VName, VAddr). This would eliminate the null value (Guideline 3) problem of Vendor.

Thus, changes to Vendor would impact both the HardwareVendor and SoftwareVendor Tables.

InstalledSoftware( CInventNum, SWInventNum);Vendor( VendorID, HWFlag, HWVendorID, SWFlag, SWVendorID);

MTE.12

CSE4701

Prob 4 – Relational Schema Analysis (Spr15)HardwareVendor ( HVendorID, HVName, HVAddr, ModelNum, ModelName, ModelDescr);SoftwareVendor (SVendorID, SVName, SVAddr, SWName, SWVersion, SWDesc);

Both tables have insert anomalies (can’t insert HWV or SWV without inserting a product), delete anomalies (if you delete the last item for a vendor you delete the vendor), and update anomalies (if you change an address, or a name – buyout, you need to change multiple tuples) – Guideline 2 is a real issue in this regard.

Both tables are representing two different entities: the contact information for a vendor (id, name, and address) and the vendors products – violating Guideline 1 for having a relation only represent a single entity. As a result, the keys are convoluted – you need to have a ModelNum for HardwareVendors and a compound key for SoftwareVendors; this makes the foreign key references more complicated.

Conclusion: As mentioned, redesign the tables Vendor, HWVendor, and SWVendor to pull out their commonalities and unify the identifier. Use Vendor as defined on the previous slide, use VendorID in the HWVendor and SWVendor tables while dropping Name and Addr from those tables.

MTE.13

CSE4701

Problem 5- Normalization (Spr15)

FULL A. {OrderID, ProductID} OrderedQuantityPART B. OrderID {OrderDate, CustID, CustName, CustAddr}TRANS C. CustID {CustName, CustAddr}PART D. ProdID {ProdDesc, UnitPrice}

INVOICE( OrderID, OrderDate, CustID, CustName, CustAddr, ProdID, ProdDesc, UnitPrice, OrderedQuantity)

Remove Partial Dependencies

ORDER_LINE( OrderID, ProdID, OrderedQuantity)PRODUCT(ProdID, ProdDesc, UnitPrice)CUST_ORDER( OrderID, OrderDate, CustID, CustName, CustAddr)

Remove Transitive Dependency in CUST_ORDER

ORDER_LINE( OrderID, ProdID, OrderedQuantity)PRODUCT(ProdID, ProdDesc, UnitPrice)ORDER( OrderID, OrderDate, CustID)CUSTOMER( CustID, CustName, CustAddr)