Problem 1. Using the INVOICE table structure shown in table below do the following: INVOICE Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value INV_NUM 211347 211347 211347 211348 211349 PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan-2016 15-Jan-2016 15-Jan-2016 15-Jan-2016 16-Jan-2016 PROD_LABEL Rotary sander 0.25-in. drill bit Band saw Rotary sander Power drill VEND_CODE 211 211 309 211 157 VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc. QUANT_SOLD 1 8 1 2 1 PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75 Write the relational schema, 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 an invoice number references more than one product. (Hint:This table uses a composite primary key.) Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created. Draw the Crow’s Foot ERD. Problem 2. Use the dependency diagram shown below to work the following problems. Initial Dependency Diagram for Problem Figure 1 Break up the dependency diagram shown in Figure 1 to create two new dependency diagrams: one in 3NF and one in 2NF. Modify the dependency diagrams you created in part a to produce a set of dependency diagrams that are in 3NF. (Hint:One of your dependency diagrams should be in 3NF but not in BCNF.) Modify the dependency diagrams you created in Part b to produce a collection of dependency diagrams that are in 3NF and BCNF.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Problem 1. Using the INVOICE table structure shown in table below do the following:
INVOICE
|
Attribute Name |
Sample Value |
Sample Value |
Sample Value |
Sample Value |
Sample Value |
|---|---|---|---|---|---|
|
INV_NUM |
211347 |
211347 |
211347 |
211348 |
211349 |
|
PROD_NUM |
AA-E3422QW |
QD-300932X |
RU-995748G |
AA-E3422QW |
GH-778345P |
|
SALE_DATE |
15-Jan-2016 |
15-Jan-2016 |
15-Jan-2016 |
15-Jan-2016 |
16-Jan-2016 |
|
PROD_LABEL |
Rotary sander |
0.25-in. drill bit |
Band saw |
Rotary sander |
Power drill |
|
VEND_CODE |
211 |
211 |
309 |
211 |
157 |
|
VEND_NAME |
NeverFail, Inc. |
NeverFail, Inc. |
BeGood, Inc. |
NeverFail, Inc. |
ToughGo, Inc. |
|
QUANT_SOLD |
1 |
8 |
1 |
2 |
1 |
|
PROD_PRICE |
$49.95 |
$3.45 |
$39.99 |
$49.95 |
$87.75 |
- Write the relational schema, 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 an invoice number references more than one product. (Hint:This table uses a composite primary key.)
- Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created.
- Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created.
- Draw the Crow’s Foot ERD.
Problem 2. Use the dependency diagram shown below to work the following problems.
Initial Dependency Diagram for Problem
Figure 1
- Break up the dependency diagram shown in Figure 1 to create two new dependency diagrams: one in 3NF and one in 2NF.
- Modify the dependency diagrams you created in part a to produce a set of dependency diagrams that are in 3NF. (Hint:One of your dependency diagrams should be in 3NF but not in BCNF.)
- Modify the dependency diagrams you created in Part b to produce a collection of dependency diagrams that are in 3NF and BCNF.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
