Database concepts

Assessment

Assignment One

For this assignment you will create a library database and write some queries for it. The library database will also form the basis of your third assignment.

This assignment has two parts

• Part A: build the database
• Part B: write the queries

Part A: Build the database

Individually:

1. Create the tables: Using the CREATE command in SQL, create the four tables in your Oracle account. Assign appropriate datatypes, sizes and whether NULL values are allowed.
   You can use the DESCRIBE command to check that you have created each of the tables correctly.
2. Populate the tables: Download the Library file. This file contains sample data for each table in text files. Also there is a .ctl control file for each table. Use SQL*Loader to populate the tables with data.
3. Add a record: Create yourself as a borrower and take a book out on loan from the Casuarina branch.

Hand ins
There are no hand ins for this part of the assignment as it will be marked in class. You need to show your working database to either Bob or Jodi.

Part B: Write the queries

In pairs:

1. Create four questions and the SQL statements that answer them. Your queries should exhibit some interesting feature of SQL such as queries over more than one relation, grouping or aggregation for example. When marking your queries we will be looking for different sorts of queries.
   
   You should run your queries on the database to make sure they work as you will be swapping your queries in class with another group.

Hand ins:
Post your questions on Learnline.
Email your SQL statements that answer your queries to hit234@cdu.edu.au.

Library Database

The following is a schema for a library system. A borrower can take a book out on loan from a library branch.

Note that there are four tables and are described as:

BORROWER

• Has five fields
• CardNo is the primary key (the borrowers library card)
• The remaining fields are
  • LName (the borrowers last name)
  • FName (the borrowers first name)
  • Suburb (suburb the borrower lives in)
  • Postcode (postcode for that suburb)

BOOK

• Has four fields
• ISBN is the primary key (International Standard Book Number)
• The remaining fields are
  • YearPublished (the year the book was published)
  • PublisherName (the name of the organisation that published the book)

LOAN

• Has five fields
• ISBN, BranchID, CardNo and DateOut are the combined primary key
  • ISBN is also a foreign key which references ISBN in the BOOK table
  • CardNo is also a foreign key which references CardNo in the BORROWER table
  • CardNo is also a foreign key which references BranchID in the BRANCH table
  • DateOut (the date the book was borrowed)
• The remaining field is
  • DateIn (the date the book was returned)

BRANCH

• Has two fields
• BranchID is the primary key (each branch has a unique number)
• The remaining field is
  • BranchName (the city, suburb or town where the branch is located)