INF3707 - Database Design and Implementation (INF3707)
Institution
University Of South Africa (Unisa)
Book
Oracle 12c: SQL
This summary contains in depth concepts, explanations and examples which will not only allow you to reduce the amount of time you have to study, but will also assist you with getting a distinction for this module. This summary will replace your prescribe book entirely!
Revolutionize Your Studying with the [Oracle 12c SQL,Casteel,3e] 2023-2024 Test Bank
INF3707 2023 assignment 3 solutions
All for this textbook (16)
Written for
University of South Africa (Unisa)
INF3707 - Database Design and Implementation (INF3707)
All documents for this subject (47)
2
reviews
By: stormwilliams • 1 year ago
By: ajbrits • 2 year ago
Seller
Follow
francoissmit
Reviews received
Content preview
Detailed
INF3707
Summary
,Description of the ‘JustLee Books’
Database
3 assumptions were made when designing the DB:
An order isn’t shipped until all items for the order are available
All addresses are in the United States
Only orders placed in the current month or orders placed in previous
months that didn’t ship are stored in the ORDERS table. At the end of
each month, all completed orders are transferred to an annual SALES
table.
The entire summary we will be dealing with the following Database:
Each of the blocks represent a table.
Explanations of the Tables:
CUSTOMER: stores information about customers who purchased a book/s
,BOOKS: Stores information about the books. The cost column is the wholesale
cost, i.e. the original cost. Then a mark-up is added and it is sold to customer as
the Retail price(indicated by the retail column)
AUTHOR: maintains a list of authors’ names
BOOKAUTHOR: Because many to many relationship between BOOKS and
AUTHOR, BOOKAUTHOR is created as bridging table.
ORDERS and ORDERITEMS: Data about customer’s orders is divided into two
tables. These 2 tables. The ORDERS table identifies which customer placed
each order. If a customer’s order included 2 or more books, the ORDERS table
could contain a repeating group, thus each order is stored in the ORDERITEMS
table.
The ORDERITEMS table is a table that have each order item as a record. Item#
field is the position in the sequence of products ordered to maintain
uniqueness in this table. It is basically like line#. line# is widely used in industry
to identify line items in an invoice.
The Paideach field records the price the customer actually pad per copy for a
specific book. This is used because prices may change for discount.
e.g. order# item# ISBN Quantity Paideach
43254 3 424-Q523 3 R430
e.g.
, This will be like line number 1
PUBLISHER: contains the publisher’s information. This table can be joined to
the BOOKS table through the field PubID which is the common field. This lined
data from these 2 tables enables you to determine which publisher to contact
when you need to reorder books by identifying which books you obtained from
each publisher.
PROMOTION: JustLee Books has an annual promotion that includes a gift with
each book purchased. The gift is based on the book’s retail price. Customers
ordering books that < $12 receive a certain gift and customers ordering books
costing between $12.01 and $25 receive a different book.
Chapter 2 Basic SQL statements:
Summary of commands in this chapter
,You can use the DESCRIBE command to view the structure of the table:
,Shows the names and the data types of all the columns. Also shows which are
Nullable, Length of the values, precision, etc.
Note: You will have to rebuild the JustLee DB as you change the DB (by
removing or adding rows). To reconstruct the DB, run the JLDB_Drop.sql script 1st to
remove any tables existing from the initial DB creation.
Then execute the JLDB_Build.sql script again to rebuild all the tables. Don’t worry if
you get of these errors: “object does not exist”
SELECT statement allows you to retrieve data from tables.
SYNTAX:
Capital letters are keywords. Each section that starts with a keyword is a
clause.
Note the following for the SELECT statement:
,* means select all data, i.e. all the columns: e.g.:
SELECT *
FROM customer;
selecting one column from a table: choosing specific columns is called
projection
SELECT title
FROM books;
Notice these are all the same so it not case sensitive and it can be entered on
more than one line:
Selecting more than one column:
,Note the column listed 1st will be displayed 1st.
Using column Aliases
It is simply giving a column another name for display purposes to give a better
description of the data.
So you substitute a column alias for a column name.
Here we list 2 columns, but instead of displaying the title column we give it an
column alias of “Title of Book” so it will display it instead of title.
If you don’t want the column alias to be all upper case then you need to use
the “” as we have done in this example. Also note, the keyword AS is optional
and can be left out. If a column alias contain a blank space then it must be
enclosed in double quotation as in the example above. Also if there are spaces
in an alias then you need to put it in “”, it tells the compiler it is 1
element/thing
If column alias consists only of 1 word without special symbols, it doesn’t need
the “ “.
Example:
,Here the retail column is assigned a column alias of PRICE. Here we don’t use
the AS optional keyword.
Note the following in the display:
Like in line 8
54.5 is displayed instead of 54.50
Using arithmetic operations:
*, /, + and – can be used with SELECT statement.
BODMAS applies to these operations
, Here we use the 2 columns retail and cost to calculate the profit. So we say
retail-cost and then assign an alias of profit to this.
NULL values
If no value is entered for a column in a row, the value is considered NULL,
indicating an absence of data.
Note in some DBMS a NULL is just blanks, but here it says null.
NULL values can lead to undesirable results in operations.
The rule is if any value in an arithmetic operation is NULL then the result is also
NULL.
e.g.
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller francoissmit. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $4.76. You're not tied to anything after your purchase.
