INF3707 - Database Design and Implementation (INF3707)
Établissement
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
Tout pour ce livre (16)
École, étude et sujet
University of South Africa (Unisa)
INF3707 - Database Design and Implementation (INF3707)
Tous les documents sur ce sujet (47)
2
revues
Par: stormwilliams • 1 année de cela
Par: ajbrits • 1 année de cela
Vendeur
S'abonner
francoissmit
Avis reçus
Aperçu du contenu
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.
Les avantages d'acheter des résumés chez Stuvia:
Qualité garantie par les avis des clients
Les clients de Stuvia ont évalués plus de 700 000 résumés. C'est comme ça que vous savez que vous achetez les meilleurs documents.
L’achat facile et rapide
Vous pouvez payer rapidement avec iDeal, carte de crédit ou Stuvia-crédit pour les résumés. Il n'y a pas d'adhésion nécessaire.
Focus sur l’essentiel
Vos camarades écrivent eux-mêmes les notes d’étude, c’est pourquoi les documents sont toujours fiables et à jour. Cela garantit que vous arrivez rapidement au coeur du matériel.
Foire aux questions
Qu'est-ce que j'obtiens en achetant ce document ?
Vous obtenez un PDF, disponible immédiatement après votre achat. Le document acheté est accessible à tout moment, n'importe où et indéfiniment via votre profil.
Garantie de remboursement : comment ça marche ?
Notre garantie de satisfaction garantit que vous trouverez toujours un document d'étude qui vous convient. Vous remplissez un formulaire et notre équipe du service client s'occupe du reste.
Auprès de qui est-ce que j'achète ce résumé ?
Stuvia est une place de marché. Alors, vous n'achetez donc pas ce document chez nous, mais auprès du vendeur francoissmit. Stuvia facilite les paiements au vendeur.
Est-ce que j'aurai un abonnement?
Non, vous n'achetez ce résumé que pour €3,44. Vous n'êtes lié à rien après votre achat.
