Group Project Homework [78 pts]
Note: This assignment can become very large, due to the screen shots. Use trimming and compression to keep it at a manageable size.
Suppose that you have designed a database for Marcia's Dry Cleaning that has the following tables:
CUSTOMER (CustomerID, FirstName, LastName, Phone, Email)
ORDER (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax, TotalAmount)
ORDER_ITEM (InvoiceNumber, ItemNumber, Service, Quantity, UnitPrice, ExtendedPrice)
SERVICE (Service, Description, UnitPrice)
Add the prefix "HW7G_" to each table name.
Do parts A through M as given below. Those that involve SQL should be written in SQL*Plus and run, unless indicated otherwise.
Include screen shots of the
…show more content…
Explain how you would enforce the data constraint that HW7G_ORDER_ITEM.UnitPrice be equal to HW7G_SERVICE.UnitPrice, where HW7G_ORDER_ITEM.Service = HW7G_SERVICE.Service. Again, no code at this time, just an explanation.
We would use a interrrelation constraint to enforce the data constraint. An interrrelation constraint can be used to set it so UnitPrice will equal each other.
F. Insert all of the following data into the tables, using the CustID Sequence where appropriate:
The data used here is essentially the same data used in Chapter Two. Note that a Sales Tax Rate is needed for calculating the ORDER.Total. This data set (as given) uses 7.90% (.079). Note that dates are not in Oracle style, so you'll have to change them when you enter them.
TABLE OF CUSTOMER DATA:
CustomerID
FirstName
LastName
Phone
Email
100
Nikki
Kaccaton
723-543-1233
NKaccaton@somewhere.com
etc.,
Brenda
Catnazaro
723-543-2344
BCatnazaro@somewhere.com as specified
Bruce
LeCat
723-543-3455
BLeCat@somewhere.com by the
Betsy
Miller
725-654-3211
BMiller@somewhere.com sequence George
Miller
725-654-4322
GMiller@somewhere.com
Kathy
Miller
723-514-9877
KMiller@somewhere.com
Betsy
Miller
723-514-8766
BMiller@elsewhere.com
TABLE OF SERVICES:
Service
Description
UnitPrice
10
Mens Shirt
1.50
11
Dress Shirt
2.50
15
Womens Shirt
1.50
16
Blouse
3.50
17
Dress Blouse
4.50
20
Mens Slacks
5.00
25
Womens Slacks
6.00
30
Step 2. Data Analysis: The data will be analyzed to determine database modeling. Step 3. Database normalization: Fields and
1.4 For the tables you created in Review Question 1.3, what are the primary keys of each table? Do you think that any of these primary keys could be surrogate keys?
Which of the accompanying is an approach to make and change tables utilizing SQL Server?
There will be many different constraints in the database. An example of a constraint is one if the students' GPA will have to be between 0.00 and 4.00. Another example would be that each class can only have one instructor. There would be a constraint on the social security number ensuring that there are only nine numbers and in the format is xxx-xx-xxxx, and each individual number would be between 0 and 9. There would be a constraint for the telephone numbers as well. Each individual number would be between 0 and 9 in the format of (xxx)-xxx-xxxx.
In order to complete this task, I will need the data that needs to be inserted into the table, and how the information will be used.
Get the most out of the constraint when it is identified, or “exploit” it. The constraint must always be managed in specifically working this part of the process.
Matching principle - the expense, merchandise and other costs, should be matched with the revenue.
1. Cash (Exhibit 7-11)............................................ $1,875 2. Gross sales revenue (Exhibit 7-9)................... $7,491 Less: Sales discounts (Exhibit 7-9)................ (35) Net sales revenue............................................. $7,456 3. Purchased furniture on account— Purchases journal p. 8 (Exhibit 7-10) 4. City Office Supply November 30 balance (Exhibit 7-11)............... $ 543
For this assignment, you will NOT create a database. You will list the type of data you want to collect, group it accordingly, break it into field and determine attributes. Think about how the different pieces of data relate to one another and what purpose they serve. You may use Excel, Word, or Visio for this assignment.
2) Assume that the shadow price of a non-binding "≤" constraint is 5. This implies that:
The main idea of this assignment is to develop an entity relationship diagram for a student who is willing to take various course in a semester. Entity relationship diagrams must be developed after converting the given data into the third normal form. I have learned how to dissolve a huge amount of data by using normal forms. I have also learned how to create an entity relationship diagram for a given data base. I have also learned various SQL queries and sub queries to retrieve data from the database. The final project of this course helped me to create a specific database on my own for a desired
A check constraint is a rule the identifies acceptable column values for data in a row within a SQL server table. Checks constraints help enforce domain integrity. Domain integrity produces an error message and the entire insert or updates operation will fail.
The objective of this lab is to teach you how to develop and use basic stored
Each foreign key of a table can become a foreign key for another table depending on the relation between those two tables.
Q 1: Perform Verify and Count Commands for all three tables and identify any exceptions.