This question comes from Database and Concepts 7th edition ISBN Number 978-0-13-

Question

This question comes from Database and Concepts 7th edition ISBN Number 978-0-13-3544626

Marcia’s Dry Cleaning Case Questions:

Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out from the competition by providing superior customer service. She wants to keep track of each of her customers and their orders. Ultimately, she wants to notify them that their clothes are ready via email.Assume that Marcia has hired you as a database consultant to develop an operational database named MDC that has the following four tables:

CUSTOMER (CustomerID, FirstName, LastName, Phone, Email)
INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax, TotalAmount)
INVOICE_ITEM (InvoiceNumber, ItemNumber, ServiceID, Quantity, UnitPrice, ExtendedPrice)
SERVICE (ServiceID, ServiceDescription, UnitPrice) A Microsoft Access 2013 version of the MDC database and SQL scripts to create and populate the MDC database are available for Microsoft SQL Server 2014, Oracle Database Express Edition 11g Release 2, and MySQL 5.6 at the Database Concepts website at www.pearsonhighered.com/kroenke. Sample data for the CUSTOMER table are shown in Figure 7-32, for the SERVICE table in Figure 7-33, for the INVOICE table in Figure 7-34, and for the INVOICE_ITEM table in Figure 7-35.

A. Create a database in your DBMS named MDC, and use the MDC SQL scripts for your DBMS to create and populate the database tables. Create a user named MDC-User with the password MDC-User+password. Assign this user to database roles so that the user can read, insert, delete, and modify data.

B. Create an appropriate ODBC data source for your database.

C. You need about 20 INVOICE transactions with supporting INVOICE_ITEMs in the database. Write the needed SQL statements for any needed additional INVOICE transactions and insert the data into your database.

D. Design a data warehouse star schema for a dimensional database named MDC-DW. The fact table measure will be ExtendedPrice.

Explanation / Answer

For the information management we needs to create a database with following statement
a. Create Database MDC ;

After Creation of Database we have to create the user and assign the rights to user
using following statement

create create user MDC identified by MDCpassword

Then create role and assign to MDC-User

create role myRole;
grant create table, alter table, drop table to myRole;
grant insert,update,delete,select on CUSTOMER,SERVICE,INVOICE, INVOICE_ITEM to myRole;
grant myRole to MDC-USER;

Then needs to create table using the script given in the website sql files

CREATE TABLE CUSTOMER (CustomerID       Int   NOT NULL,
       FirstName        Char(25)       NOT NULL,
       LastName       Char(25)   NOT NULL,
       Phone           Char(12)       NOT NULL,
       Email              Char(100)       NULL,
       CONSTRAINT       Customer_PK   PRIMARY KEY (CustomerID)
       );
CREATE SEQUENCE seqCID INCREMENT BY 5 START WITH 100;

CREATE TABLE SERVICE (ServiceID   Int      NOT NULL,
       ServiceDescription   Char(50) NOT NULL,
       UnitPrice       Number(8,2)   NOT NULL,
       CONSTRAINT          Service_PK        PRIMARY KEY(ServiceID),
       CONSTRAINT          ServicePrice      CHECK
       ((UnitPrice >= 1.50) AND (UnitPrice <= 10.00))
       );

CREATE TABLE INVOICE (InvoiceNumber       Int       NOT NULL,
       CustomerID          Int               NOT NULL,
       DateIn              Date           NOT NULL,
       DateOut       Date       NULL,
       Subtotal       Number(8,2)   NULL,
       Tax           Number(8,2)   NULL,
       TotalAmount       Number(8,2)   NULL,
       CONSTRAINT       Invoice_PK       PRIMARY KEY (InvoiceNumber),
       CONSTRAINT         InvoiceCustomerFK FOREIGN KEY(CustomerID)
                   REFERENCES CUSTOMER(CustomerID));

CREATE TABLE INVOICE_ITEM (
       InvoiceNumber Int   NOT NULL,
       ItemNumber      Int    NOT NULL,
       ServiceID      Int   NOT NULL,  
    Quantity      Int   DEFAULT 1 NOT NULL,
       UnitPrice      Number(8,2)   NULL,
       ExtendedPrice   Number(8,2)   NULL,
       CONSTRAINT       InvoiceItem_PK PRIMARY KEY(InvoiceNumber, ItemNumber),   CONSTRAINT       InvoiceItemFK   FOREIGN KEY(InvoiceNumber)
       REFERENCES INVOICE(InvoiceNumber) ON DELETE CASCADE,
       CONSTRAINT       ItemServiceFK   FOREIGN KEY(ServiceID) REFERENCES Service(ServiceID) );

B. We can create the ODBC data Source for Database using the following steps

Open Control panel of Windows, Click on Administrative Tools then ODBC Driver another dialog box will open
click on System DNS Button click on Add Button and select the appropriate driver like oracle then click on ok button

C. We can add Record in Table using insert into statement for all tables. we can use the statements given on website sample script as it is and extend the number of statement as per given requirements and given data on your book figure
INSERT INTO INVOICE VALUES(
       2014001, 100, '04-Oct-14', '06-Oct-14', 158.50, 12.52, 171.02);
INSERT INTO INVOICE VALUES(
       2014002, 105, '04-Oct-14', '06-Oct-14', 25.00, 1.98, 26.98);
INSERT INTO INVOICE VALUES(
       2014003, 100, '06-Oct-14', '08-Oct-14', 49.00, 3.87, 52.87);
INSERT INTO INVOICE VALUES(
       2014004, 115, '06-Oct-14', '08-Oct-14', 17.50, 1.38, 18.88);
INSERT INTO INVOICE VALUES(
       2014005, 125, '07-Oct-14', '11-Oct-14', 12.00, 0.95, 12.95);
INSERT INTO INVOICE VALUES(
       2014006, 110, '11-Oct-14', '13-Oct-14', 152.50, 12.05, 164.55);
INSERT INTO INVOICE VALUES(
       2014007, 110, '11-Oct-14', '13-Oct-14', 7.00, 0.55, 7.55);
INSERT INTO INVOICE VALUES(
       2014008, 130, '12-Oct-14', '14-Oct-14', 140.50, 11.10, 151.60);
INSERT INTO INVOICE VALUES(
       2014009, 120, '12-Oct-14', '14-Oct-14', 27.00, 2.13, 29.13);

INSERT INTO INVOICE_ITEM VALUES(2014001, 1, 16, 2, 3.50, 7.00);
INSERT INTO INVOICE_ITEM VALUES(2014001, 2, 11, 5, 2.50, 12.50);
INSERT INTO INVOICE_ITEM VALUES(2014001, 3, 50, 2, 10.00, 20.00);
INSERT INTO INVOICE_ITEM VALUES(2014001, 4, 20, 10, 5.00, 50.00);
INSERT INTO INVOICE_ITEM VALUES(2014001, 5, 25, 10, 6.00, 60.00);
INSERT INTO INVOICE_ITEM VALUES(2014001, 6, 40, 1, 9.00, 9.00);
INSERT INTO INVOICE_ITEM VALUES(2014002, 1, 11, 10, 2.50, 25.00);
INSERT INTO INVOICE_ITEM VALUES(2014003, 1, 20, 5, 5.00, 25.00);
INSERT INTO INVOICE_ITEM VALUES(2014003, 2, 25, 4, 6.00, 24.00);
INSERT INTO INVOICE_ITEM VALUES(2014004, 1, 11, 7, 2.50, 17.50);
INSERT INTO INVOICE_ITEM VALUES(2014005, 1, 16, 2, 3.50, 7.00);
INSERT INTO INVOICE_ITEM VALUES(2014005, 2, 11, 2, 2.50, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2014006, 1, 16, 5, 3.50, 17.50);
INSERT INTO INVOICE_ITEM VALUES(2014006, 2, 11, 10, 2.50, 25.00);
INSERT INTO INVOICE_ITEM VALUES(2014006, 3, 20, 10, 5.00, 50.00);
INSERT INTO INVOICE_ITEM VALUES(2014006, 4, 25, 10, 6.00, 60.00);
INSERT INTO INVOICE_ITEM VALUES(2014007, 1, 16, 2, 3.50, 7.00);
INSERT INTO INVOICE_ITEM VALUES(2014008, 1, 16, 3, 3.50, 10.50);
INSERT INTO INVOICE_ITEM VALUES(2014008, 2, 11, 12, 2.50, 30.00);
INSERT INTO INVOICE_ITEM VALUES(2014008, 3, 20, 8, 5.00, 40.00);
INSERT INTO INVOICE_ITEM VALUES(2014008, 4, 25, 10, 6.00, 60.00);
INSERT INTO INVOICE_ITEM VALUES(2014009, 1, 40, 3, 9.00, 27.00);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.