Question 2 James Company has the following information related to its manufacturing and selling of staplers.

COURSERA ASSIGNMENT

-SAI HRUSHITHA ROLLNUM-221810303034

WEEK-5

MODULE -8

1.Draw an ERD to track lab tests performed by a medical laboratory on clients. The database should track basic client details including a unique client identifier, client name, client insurance provider (if any), client address, client date of birth, and client sex. The database should track the unique identifier for a lab test, the test type identifier, the date and time when the lab test was administered, and the identifier of the lab employee performing the test. A client can request multiple tests in a visit to the lab. The database only contains clients who have had lab tests performed. Each lab test is administered to one client.

2.Revise the ERD from problem 1 with more details about test types. A test type includes a unique test type identifier, a test type name, a test type cost, and a test type code. A lab test administered to a client is associated with one test type. A test type can be administered to multiple clients. A test type can exist in the database without ever being administered.

3.Revise the ERD from problem 2 with test type items. A test type item includes a unique test item identifier, a test item name, test item unit of measure, and a test item description. A test type includes one or more test items. A test item can be part of one or more test types. Do not use an M-N relationship.

1. Revise the ERD from problem 3 to use an M-N relationship.

MODULE-9

1. Requirements for Data Modeling Problems

1. For the following problem, define an ERD for the initial requirements and then revise the ERD for the new requirements. Your solution should have an initial ERD, a revised ERD, and a list of design decisions for each ERD. In performing your analysis, you may want to follow the approach presented in module 9.

Design a database for managing the task assignments on a work order. A work order records the set of tasks requested by a customer at a specified location.

• A customer has a unique customer identifier, a name, a billing address (street, city, state, and zip), and a collection of submitted work orders.

• A work order has a unique work order number, a creation date, a date required, a completion date, a customer, an optional supervising employee, a work address (street, city, state, zip), and a set of tasks.

• Each task has a unique task identifier, a task name, an hourly rate, and estimated hours. Tasks are standardized across work orders so that the same task can be performed on many work orders.

• Each task on a work order has a status (not started, in progress, or completed), actual hours, and a completion date. The completion date is not entered until the status changes to complete.

After reviewing your initial design, the company decides to revise the requirements. Make a separate ERD to show your refinements. Refine your original ERD to support the following new requirements:

• The company wants to maintain a list of materials. The data about materials include a unique material identifier, a name, and an estimated cost. A material can appear on multiple work orders.

• Each work order uses a collection of materials. A material used on a work order includes the estimated quantity of the material and the actual quantity of the material used.

• The estimated number of hours for a task depends on the work order and task, not on the task alone. Each task of a work order includes an estimated number of hours.

BEFORE REVISE

AFTER REVISE

2.

WEEK-6

MODULE-10

1. For the ERD in Figure 1, you should indicate the applications of the entity type rule. For each entity type rule application, you should identify the table name, primary key, and other columns. You do not need to write CREATE TABLE statements.

ANS—

Student(StdNo, StdName, StdAddress, StdCity, StdState, StdZip, StdEmail)

.Table Name: Student

Primary Key: StdNo

Columns: Attributes listed

Loan(LoanNo, ProcDate, DisbMethod, DisbBank, DateAuth, NoteValue, Subsidized, Rate)

.Table Name: Loan

Primary Key: LoanNo

Columns: Attributes listed

Institution(InstID, InstName, InstMascot)

.Table Name: Institution

Primary Key: InstID

Columns: Attributes listed

Lender(LenderNo, LendName)

.Table Name: Lender

Primary Key: LenderNo

Columns: Attributes listed

1. For the ERD in Figure 1, you should indicate applications of the 1-M relationship rule. For each 1-M relationship rule application, you should indicate the changes to the tables you listed in problem 1 including foreign key columns and NOT NULL constraints for foreign keys if necessary.

ANS—

Loan(LoanNo, StdNo, LenderNo, InstID, ProcDate, DisbMethod, DisbBank, DateAuth, NoteValue, Subsidized, Rate)

>FOREIGN KEY(StdNo) REFERENCES Student

>FOREIGN KEY(LenderNo) REFERENCES Lender

>FOREIGN KEY(InstID) REFERENCES Institution

>StdNo NOT NULL

>LenderNo NOT NULL

>InstID NOT NULL

3.For the ERD in Figure 1, you should indicate applications of the M-N relationship rule. For each M-N relationship rule application, you should list the table name, primary key, and other columns.

Ans :There are NONE.

4.For the ERD in Figure 1, you should indicate applications of the identifying relationship rule. For each identifying relationship rule application, you should indicate the changes to the tables you listed in problem 2.

ANS—

DisburseLine(DateSent, LoanNo, Amount, OrigFee, GuarFee)

FOREIGN KEY(LoanNo) REFERENCES Loan

LoanNo NOT NULL

I altered the weak entity DisburseLine into its own table. Its primary key is a combination of DateSent and LoanNo.

5.Convert the ERD shown in Figure 2 into tables. List the conversion rules used and table design. For each table, you should list the primary key, foreign keys, other columns, and NOT NULL constraints for foreign keys if necessary. You do not need to write CREATE TABLE statements.

Figure 2: ERD for Conversion Problem 5

ANS--

The following table conversion uses the Entity Type Rule and the 1-M Rule.

Account(Acctid, AcctName, Balance, Decomposed)

Primary Key: Acctid

FOREIGN KEY(Decomposed) REFERENCES Account

Columns: Attributes listed

6.Convert the ERD shown in Figure 3 into tables. List the conversion rules used and table design. For each table, you should list the primary key, foreign keys, other columns, and NOT NULL constraints for foreign keys if necessary. You do not need to write CREATE TABLE statements.

Figure 3: ERD for Conversion Problem 6

ANS—

Owner(OwnId, OwnName, OwnPhone)

Primary Key: OwnId

Property(PropId, BldgName, UnitNo, Bdrms)

Primary Key: PropId

Shares(PropId, OwnId, StartWeek, EndWeek)

Primary Key: PropId and OwnId

FOREIGN KEY(PropId) REFERENCES Property

FOREIGN KEY(OwnId) REFERENCES Owner

Columns: Attributes listed