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.
