hw1solutions
9 Pages

hw1solutions

Course Number: CIS 331, Fall 2009

College/University: Temple

Word Count: 1633

Rating:

Document Preview

CIS 331 6/12/2002 PROBLEM 1 HOMEWORK 1 SOLUTIONS The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for its training program. The HEG administrator gives you the following description of the training group's operating environment: The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five...

Unformatted Document Excerpt
Coursehero >> Pennsylvania >> Temple >> CIS 331

Course Hero has millions of student submitted documents similar to the one
below including study guides, practice problems, reference materials, practice exams, textbook help and tutor support.

Course Hero has millions of student submitted documents similar to the one below including study guides, practice problems, reference materials, practice exams, textbook help and tutor support.

331 CIS 6/12/2002 PROBLEM 1 HOMEWORK 1 SOLUTIONS The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for its training program. The HEG administrator gives you the following description of the training group's operating environment: The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five "advanced technology" courses, each of which may generate several classes. If a class has fewer than 10 trainees in it, it will be canceled. It is, therefore, possible for a course not to generate any classes during a session. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research. Each trainee may take up to two classes per session. Given this information, do the following: a. Draw the E-R diagram for HEG. b. Describe the relationship between instructor and course in terms of connectivity, cardinality, and existence dependence. Both questions, a and b, have been addressed in the following E-R diagram. Basically, three sets of relationships exist: A COURSE may generate one or more CLASSes, an INSTRUCTOR teaches up to two CLASSes, and a TRAINEE may enroll in up to two CLASSes. A trainee can take more than one class, and each class contains many (10 or more) trainees, so there is a M:N relationship between TRAINEE and CLASS. (We must, therefore, create a composite entity to serve as the bridge between TRAINEE and CLASS.) A class is taught by only one instructor, but an instructor can teach up to two classes. Therefore, there is a 1:M relationship between INSTRUCTOR and CLASS. Finally, a COURSE may generate more than one CLASS, while each CLASS is based on one COURSE, so there is a 1:M relationship between COURSE and CLASS. These relationships are all reflected in the following E-R diagram. Note the optional and mandatory relationships: to exist, a CLASS must have TRAINEEs enrolled in it, but TRAINEEs do not necessarily take CLASSes. (Some may take "on the job training.") An INSTRUCTOR may not be teaching any CLASSes, doing research instead, but each CLASS must have an INSTRUCTOR. If not enough people sign up for a CLASS, a COURSE may not generate any CLASSes, but each CLASS must represent a COURSE. Figure Q3.8 The E-R Diagram for HEG 1 INSTRUCTOR (0,2) teaches M (1,1) CLASS M (1,1) generates (0,N) (10,30) (1,1) 1 COURSE 1 M (1,1) ENROLL TRAINEE (0,2) 1 M Note: To decrease the level of abstraction in the preceding E-R diagram, we may show the connectivities for the bridge entity and rename the bridge entity (composite entity) to reflect its purpose. Note also that the sentences "HEG has twelve instructors." and "HEG offers five advanced technology courses." are not reflected in the E-R diagram. Instead, they represent additional information concerning the number of entities in an entity set, rather than information concerning entity relationships. PROBLEM 2. Figure P3.1 DIVISION operates DEPARTMENT employs manages runs EMPLOYEE is assigned to PROJECT 1. Use the following business rules to write all appropriate connectivities in the E-R diagram: one a. A department employs many employees, but each employee is employed by department. The answers to question 1 (all parts) are included in the E-R diagram that accompanies Problem 2. b. Some employees, known as "rovers," are not assigned to any department. c. A division operates many departments, but each department is operated by one division d. An employee may be assigned to many projects, and a project may have many employees assigned to it. e. A project must have at least one employee assigned to it. f. One of the employees manages each department, and each department is managed by only one employee. g. One of the employees runs each division, and each division is run by one employee. 2. Write all the cardinalities into the model. The answer to question 2 is included in the E-R diagram that accompanies Problem 2. 3. Modify the E-R model by splitting the M:N relationship into two 1:M relationships that are connected through a composite entity. Then rewrite the connectivities and cardinalities to match the changes you have made. The completed Chen ERD is shown in Figure P3.3. Note that there are two relationships between DEPARTMENT and EMPLOYEE. Figure P3.3 The Completed Chen ERD for Problem2. 1 DIVISION 1 (1,1) (1,N) operates M DEPARTMENT (1,1) (1,N) 1 1 (1,1) employs 1 runs (0,1) (0,1) M 1 manages EMPLOYEE (0,N) 1 (1,1) M ASSIGN M (1,1) (1,N) 1 PROJECT (0,1) Discussion: Note that the ERD shown in Figure P3.3a and in the Crow's Foot ERD shown in Figure P3.3b -- reflects several useful features that become especially important when the design is implemented. For example: The ASSIGN entity is shown to be optional to the PROJECT. This decision makes sense from a practical perspective, because it lets you create a new project record without having to create a new assignment record. (If a new project is started, there will not yet be any assignments.) The relationship expressed by "DEPARTMENT employs EMPLOYEE" is shown as mandatory on the EMPLOYEE side. This means that a DEPARTMENT must have at least one EMPLOYEE in order have to departmental status. However, DEPARTMENT is optional to EMPLOYEE, so an employee can be entered without entering a departmental FK value. If the existence of nulls is not acceptable, you can create a "No assignment" record in the DEPARTMENT table, to be referenced in the EMPLOYEE table if an employee is not assigned to a department. Note also the implications of the 1:1 "EMPLOYEE manages DEPARTMENT" relationship. The flip side of this relationship is that "each DEPARTMENT is managed by one EMPLOYEE". (This latter relationship is shown as mandatory in the ERD. That is, each department must be managed by an employee!) Therefore, one of the EMPLOYEE table's PK values must appear as the FK value in the DEPARTMENT table. (Because this is a 1:1 relationship, the index property of the EMP_NUM FK in the DEPARTMENT table must be set to "unique.") Although you ought to approach a 1:1 relationship with caution most 1:1 relationships are the result of a misidentification of attributes as entities the 1:1 relationships reflected in the "EMPLOYEE manages DEPARTMENT" and "EMPLOYEE runs DISISION" are appropriate. These 1:1 relationships avoid the data redundancies you would encounter if you duplicated employee data such a names, phones, and e-mail addresses in the DIVISION and DEPARTMENT entities. 4. Convert the Chen model you have developed in problems 1-3 to a Crow's Foot model. Include at least the minimum number of attributes required to implement the model. If you develop the Crow's Foot ERD shown in Figure P3.4, there are some important Crow's Foot features to keep in mind. If you have multiple relationships between two entities -- such as the "EMPLOYEE manages DEPARTMENT" and "DEPARTMENT employs EMPLOYEE" relationships you must make sure that each relationship has a designated primary entity. For example, the 1:1 relationship expressed by "EMPLOYEE manages DEPARTMENT" requires that the EMPOYEE entity be designated as the primary (or "first") entity. If you use Visio to create your Crow's Foot ERDs, Figures P3.4a and 4b show how the 1:1 relationship is specified. If you use some other CASE tool, you will discover that it, too, is likely to require similar relationship specifications. The Crow's Foot ERD in Figure P3.4 contains attribute information that is not available in the Chen model. In addition, the nature of the relationships (identifying or non-identifying) is immediately obvious. Note also that the ASSIGN entity was renamed ASSIGNMENT the Crow's Foot entity specification provided plenty of room to expand the name to its proper noun specification. (ASSIGN might be interpreted as a verb.) Figure P3.4 The Completed Crow's Foot ERD for Problem 2 Figure P3.4a Visio Cardinality Designation Figure P3.4b Visio FK Designation PROBLEM 3 1. Using the following INVOICE table structure, draw its dependency diagram and identify all dependencies (including all partial and transitive dependencies). You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.) Attribute name INV_NUM PROD_NUM SALE_DATE PROD_DESCRIPTION VEND_CODE VEND_NAME NUMBER_SOLD PROD_PRICE Sample value 211347 AA_E3422QW 06/25/1999 B&D Rotary sander, 6 in. disk 211 NeverFail, Inc. 2 $49.95 The solution to this problem is combined with the solution to Problem 3.2 to let you trace the decomposition process in a single slide. 2. Using the initial dependency diagram drawn in problem 3.1, remove all partial dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure you created. Note: You can assume that any given product is supplied by a single vendor, but a vendor can supply many products. Therefore, it is proper to conclude that the following dependency exists: PROD_NUM PROD_DESCRIPTION, PROD_PRICE, VEND_CODE, VEND_NAME (Hint: Your actions should produce three new dependency diagrams.) The solution to both problem 3.1 and 3.2 is shown in Figure P4.1&2. Figure P4.1&2 The Dependency Diagrams for Problems 1 and 2 Problem 1 Solution INV_NUM PROD_NUM SALE_DATE PROD_DESCRIPTION VEND_CODE VEND_NAME NUM_SOLD PROD_PRICE Partial dependency Transitive Dependency Partial dependency Problem 2 Solution INV_NUM PROD_NUM NUM_SOLD 3NF INV_NUM SALE_DATE 3NF PROD_NUM PROD_DESCRIPTION PROD_PRICE VEND_CODE VEND_NAME 2NF (Contains a transitive dependency) Transitive Dependency 3. Using the table structures you have created in problem 3.2, remove all transitive dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure you created. To illustrate the effect of Problem 3.3's complete decomposition, we have shown Problem 1's dependency diagram again in Figure P4.3. Figure P4.3 The Dependency Diagram for Problem 3.3 Problem 1 Solution INV_NUM PROD_NUM SALE_DATE PROD_DESCRIPTION VEND_CODE VEND_NAME NUM_SOLD PROD_PRICE Partial dependency Transitive Dependency Partial dependency Problem 3 Solution INV_NUM PROD_NUM NUM_SOLD 3NF INV_NUM SALE_DATE 3NF PROD_NUM PROD_DESCRIPTION PROD_PRICE VEND_CODE 3NF VEND_CODE VEND_NAME 3NF

Find millions of documents on Course Hero - Study Guides, Lecture Notes, Reference Materials, Practice Exams and more. Course Hero has millions of course specific materials providing students with the best way to expand their education.

Below is a small sample set of documents:

Temple - CIS - 331
CIS 331 Out: 6/21/2002 Due: 6/28/2002 Problem 1 (40p)HOMEWORK 3Section 12Consider AIRLINE relational database shown at figure, which describes a database for airline flight information. Each FLIGHT is identified by a flight NUMBER and consists
Wisconsin - ECE - 734
Doc. A/52 10 Nov 94 12 Apr 95 24 May 95 20 Dec 95DIGITAL AUDIO COMPRESSION STANDARD (AC-3)ADVANCED TELEVISION SYSTEMS COMMITTEE James C. McKinney, Chairman Dr. Robert Hopkins, Executive DirectorATSCDigital Audio Compression (AC-3) Standard1
Wisconsin - ECE - 734
JPEG File Interchange FormatVersion 1.02September 1, 1992Eric Hamilton C-Cube Microsystems 1778 McCarthy Blvd. Milpitas, CA 95035 +1 408 944-6300 Fax: +1 408 944-6314 E-mail: eric@c3.pla.ca.usJPEG File Interchange FormatVersion 1.02Why a Fil
Wisconsin - ECE - 734
PDF created with pdfFactory Pro trial version www.pdffactory.comPDF created with pdfFactory Pro trial version www.pdffactory.comPDF created with pdfFactory Pro trial version www.pdffactory.comPDF created with pdfFactory Pro trial version www.pd
Maryville MO - ELE - 106
Team MausTeam MembersJoe Malo Josh Mele Alaric Bobby Faizaan MustafaProject Goals1. 2. 3. 4.Search or wander the maze. Build a map of what it sees. Drop cargo at the end point. Return to start using the shortest path.Commands, Output,
Maryville MO - ELE - 480
Real-Time Full High-Definition Digital Compression DeviceBackground to the Project The objective of this project is to design and develop a system to compress and then decompress real-time video data. The video data is full high-definition resolutio
E. Kentucky - MATH - 116
#!/usr/local/bin/perl#This is a perl script for trapizoidal approximation.#Unless you konw something about perl script, you will need some help.#To run this program, save it as trapiz.pl (or as any dot-pl file). #You will change the function in
Columbia - MW - 2230
Table 1: Statistics for Several Policy Rulesa b c Var(R) Var(Y) Var() Var(p) Var(-E) Var{E(Y-Ys)} L * L+*2Contemporaneous-data Taylor rules: A0 3.00 0.80 1.00 B0 1.20 1.00 1.00 C0 1.50 0.50 0.00 D0 1.50 1.00 0.00 E0 1.22 0.06 1.28 F0 2.88 0.02 0.
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: f2Tc8.ps %CreationDate: 05/01/98 16:47:16 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: f2Tc2.ps %CreationDate: 05/01/98 16:56:04 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: f2Tc5.ps %CreationDate: 05/01/98 17:05:12 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: f2Tc1.ps %CreationDate: 05/01/98 17:06:21 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: f2Tc3.ps %CreationDate: 05/01/98 16:54:50 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTb2x.ps %CreationDate: 05/04/98 17:03:39 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTb10x.ps %CreationDate: 05/04/98 17:01:24 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComment
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTb9x.ps %CreationDate: 05/04/98 17:02:28 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTb1x.ps %CreationDate: 05/04/98 17:03:52 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTb6x.ps %CreationDate: 05/04/98 17:03:15 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTb8x.ps %CreationDate: 05/04/98 17:02:51 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTLc2x.ps %CreationDate: 05/05/98 00:26:25 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComment
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTLb2x.ps %CreationDate: 05/04/98 17:38:48 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComment
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTLb8.ps %CreationDate: 05/04/98 17:37:42 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fTPb8x.ps %CreationDate: 05/05/98 11:56:36 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComment
Columbia - MW - 2230
%!PS-Adobe-2.0 %Creator: MATLAB, The Mathworks, Inc. %Title: fig.ps %CreationDate: 05/05/98 12:39:35 %DocumentNeededFonts: Helvetica %+ Times-Roman %DocumentProcessColors: Cyan Magenta Yellow Black %Pages: (atend) %BoundingBox: (atend) %EndComments %
BYU - CS - 584
Using the BYU SP-2Our System Interactive nodes (2) used for login, compilation & testing marylou10.et.byu.edu I/O and scheduling nodes (7) used for the batch scheduling system and the parallel file system Compute nodes (26) 22 4 4 processo
SUNY Buffalo - CSE - 562
CSE 562: Project #1 (due October 5, 2006)Submit all work in electronic form using submit cse562. This is individual work.ProjectThe databaseYou are given the following relational schema (keys underlined): Passenger(PassId,Name,Phone) Flight(Air
Berkeley - MCB - 110
Innate immunity mediated by APOBEC3GExpression of APOBEC3G in the virus-producing cell (left) can lead to abortive retroviral infection of the target cell (right). APOBEC3G is incorporated into progeny virions as they assemble and bud. After entry
UGA - MYWEB - 501
Chapter 15: Stockholders' Equity: Contributed CapitalThe balance sheet shows assets (left side) and claims to assets (right side). Claims to assets are divided into liabilities and equity accounts. We discussed liabilities in chapters 13 and 14. Cha
Maryville MO - CONNT - 95002
Maryville MO - FLSGPW - 00001
Maryville MO - AKUW - 82002