Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

For each of the following situations, design a database to hold the specified information drawing an entity- relationship (ER) diagram and writing a database design

image text in transcribed

image text in transcribed

For each of the following situations, design a database to hold the specified information drawing an entity- relationship (ER) diagram and writing a database design outline for the database required to hold the information described in the problem. A database design outline is a description of a database using the following notation (shown here for an example we used in class). Primary key fields must be underlined and foreign keys annotated with "attributeforeign key to TABLE". CUSTOMER(CustomerID, FirstName, LastName, StreetAddress, City, State, Zip, Phone) LOAN(Loan D, Date, Loan Amount, Rate, Term, Type, CustID) CustID foreign key to CUSTOMER PAYMENT(LoanID, PaymentNumber, Date, Payment Amount) LoanID foreign key to LOAN In your answers, all tables must have a primary key; you may add synthetic-key "ID" fields to tables whenever necessary. An entity-relationship (ER) diagram means a graphical depiction of the database structure as given in class. For the example above, the diagram would be as follows: M CUSTOMER Takes out Takes out LOAN Made on PAYMENT In each question below, assume that any "address information" specified is in U.S. format, consisting of fields for street address, city, state code, and zip code. Assume that you do not have the master zip code table in your databases, so that city, state, and zip code may be treated as independent (this issue will be discussed in class on February 11). Note that any text in the diamonds (such as "takes out" and "made on" above) is essentially a comment relating the database structure to the business situation, so exactly what you write in the diamonds is not critical. When the problems specify that you should store a "date/time", assume that you will store dates and times together in MS-Access-style "date/time" fields. Question 3. (30 points) You operate the PC computer support group for a medium-sized company. You wish to keep records of the time your group spends servicing various personal computers within the firm. Each company-owned PC is identified by a unique property tag number; in addition, you want to record the PC's make and model, the room number where it is located, and the date it was acquired. Each computer "belongs" to a department of the company, identified by a department code. Each department also has a name. Some departments have only a few computers, but some have as many as a hundred. Every time one of your technicians services a computer, you want to record which computer was serviced, which technician performed the service, the date/time the service started, the date/time it ended, and a description of the work performed. Each technician is identified by an employee ID number; for each technician, you also want to store their first name, middle name/initial, last name, and date hired. For each of the following situations, design a database to hold the specified information drawing an entity- relationship (ER) diagram and writing a database design outline for the database required to hold the information described in the problem. A database design outline is a description of a database using the following notation (shown here for an example we used in class). Primary key fields must be underlined and foreign keys annotated with "attributeforeign key to TABLE". CUSTOMER(CustomerID, FirstName, LastName, StreetAddress, City, State, Zip, Phone) LOAN(Loan D, Date, Loan Amount, Rate, Term, Type, CustID) CustID foreign key to CUSTOMER PAYMENT(LoanID, PaymentNumber, Date, Payment Amount) LoanID foreign key to LOAN In your answers, all tables must have a primary key; you may add synthetic-key "ID" fields to tables whenever necessary. An entity-relationship (ER) diagram means a graphical depiction of the database structure as given in class. For the example above, the diagram would be as follows: M CUSTOMER Takes out Takes out LOAN Made on PAYMENT In each question below, assume that any "address information" specified is in U.S. format, consisting of fields for street address, city, state code, and zip code. Assume that you do not have the master zip code table in your databases, so that city, state, and zip code may be treated as independent (this issue will be discussed in class on February 11). Note that any text in the diamonds (such as "takes out" and "made on" above) is essentially a comment relating the database structure to the business situation, so exactly what you write in the diamonds is not critical. When the problems specify that you should store a "date/time", assume that you will store dates and times together in MS-Access-style "date/time" fields. Question 3. (30 points) You operate the PC computer support group for a medium-sized company. You wish to keep records of the time your group spends servicing various personal computers within the firm. Each company-owned PC is identified by a unique property tag number; in addition, you want to record the PC's make and model, the room number where it is located, and the date it was acquired. Each computer "belongs" to a department of the company, identified by a department code. Each department also has a name. Some departments have only a few computers, but some have as many as a hundred. Every time one of your technicians services a computer, you want to record which computer was serviced, which technician performed the service, the date/time the service started, the date/time it ended, and a description of the work performed. Each technician is identified by an employee ID number; for each technician, you also want to store their first name, middle name/initial, last name, and date hired

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

SQL Server T-SQL Recipes

Authors: David Dye, Jason Brimhall

4th Edition

1484200616, 9781484200612

More Books

Students also viewed these Databases questions

Question

What is the total annual turnover rate?

Answered: 1 week ago

Question

=+applying to all or most employers and employees?

Answered: 1 week ago