PJ 1 Manual Queries You must answer all the query questions in the Report section of
Question:
- PJ 1 – Manual Queries
You must answer all the query questions in the Report section of this document.
BITS database has the following tables containing all the data records as shown below.
- The above is the Entity Relationship diagram (ERD) of the BITS database. Each relationship is a one-to-many relationship from a primary key (PK) to a foreign key (FK). One OrderNum (from WorkOrders table) may have many OrderNum rows (in OrderLine table). One TaskID (from Tasks table) may have many TaskID rows (in OrderLine table). The primary key of OrderLine table is OrderNum and TaskID. Therefore, the relationship of OrderNum and TaskID (in OrderLine table) is many-to-many.
- In other words, one OrderNum may have many TaskIDs, and one TaskID may appear in many OrderNums.
- “Many” means 0, 1, 2, or more.
- The database structures of the BITS database are specified as follows:
- For readability, all PKs are underlined and bold, and all FKs are identified in red.
Consultant (ConsltNum, LastName, FirstName, Street, City, State, ZipCode, Hours, Rate)
PK: ConsltNum
Client (ClientNum, ClientName, Street, City, State, ZipCode, Balance, CreditLimit, ConsltNum)
PK: ClientNum
FK1: ConsltNum references Consultant table
Tasks (TaskID, Description, Category, Price)
PK: TaskID
WorkOrders (OrderNum, OrderDate, ClientNum)
PK: OrderNum
FK1: ClientNum references Client table
OrderLine (OrderNum, TaskID, ScheduledDate, QuotedPrice)
PK: OrderNum, TaskID
FK1: OrderNum references WorkOrders table
FK2: TaskID references Tasks table
Note: To show the database structures, you must underline and bold primary key (PK) fields to show the PK of each table. You must also show all foreign key (FK) fields in red for each table. You do not show any secondary key (SK) fields here because SKs are not mandatory.
- =========================================================================.
- How to submit your project assignment (PJ 1)?
- You must submit to Canvas the following item as an attachment:
- Your WORD document (for example, CiS120-PJ1-report-my-name.docx) that contains the
following report.
- =========================================================================.
* You must delete everything above & including this line to make this your Word document to be submitted.
PJ 1 Report My Name: Jessica Chairez
You must answer all the following 12 questions based on the BITS database as shown above.
In a relational database, each query result is really a table with at least one column.
You do not use Access DBMS for this project yet.
You must not show/list any other columns not requested by the user’s query.
You must not show/list duplicate data to confuse the users.
Q0 is the sample query for you to learn and follow.
Q0. (Sample) List LastName and FirstName of those consultants who do not serve any clients.
Answer:
LastName FirstName |
Shields Tom |
Q1. List the names of all clients that have a credit limit >= $10,000
[Hint: The answer contains 3 names. One name is “Two Crefty Cousins”.
Q2. List the descriptions of all items in the Tasks table that have the category DRM.
[Hint: The answer contains 2 descriptions. One description is “Data Recovery Major”.]
Q3. List the order numbers for orders placed by client number 322 on September 10, 2018.
[Hint: The answer contains 2 numbers. One number is 67424.]
Q4. List the order number, the order date, and the scheduled date for any work order involving
TaskID SA44. (You must show a table for this query result.)
[Hint: The answer contains only one record.]
Q5. List the name of each client who placed an order that has exactly two different tasks in the
OrderLine table. [Hint: The answer contains 2 names. One name is “Harpersburg Bank”.]
Q6. List the name of each client who has a credit limit of $5,000, and is represented by the consultant
Patrick Jordan. [Hint: The answer contains 2 names. One name is “Howler, Laura”.]
Q7. List the sum of the balances for all customers represented by the consultant Christopher Turner.
(The result must be only one big number, not a list of numbers.)
Q8. For each order placed on September 10, 2018, list the order number, the client name, the last and first
name of its consultant. (You must show a table for this query result.)
[Hint: The answer contains 3 records. One record is as follows:
“67838 MarketPoint Sales Turner Christopher”]
Q9. List the name of each client who placed an order that has exactly three different tasks in the
OrderLine table. [Hint: The answer contains only one name. The name begins with “P”.]
Q10. List ClientNum, ConsltNum, and City for each client whose city is the same as the city of its
consultant. (You must show a table for this query result.)
[Hint: The answer contains 2 records. One record is “322 35 Lizton”.]
Q11. List the ClientNum and the ClientName for each client whose consultant is “Christopher Turner”,
and who has ordered only one work order so far. (You must show a table for this query result.)
[Hint: The answer contains only one record.]
Q12. BITS company would like to be able to contact clients when problems arise concerning any order. Please recommend some new attributes or columns or fields, which should be added to the Client table to assist in contacting the clients.
Accounting Information Systems
ISBN: 978-1133935940
10th edition
Authors: Ulric J. Gelinas, Richard B. Dull