Homework
Module 1 - SLP
Data, Database, Database Management System, and Relational Database Modeling
The SLP Project in CSC316 spans from Module 1 to Module 4. You are required to apply what you have learned in previous modules to the assignment in the current module. That is, the Module 2 SLP is built on the Module 1 SLP assignment, and the Module 3 SLP is built on the Module 2 assignment, etc. Please follow the instructions below closely:
Problem Definition
A used-book store has been in business for over a month. The owners have been so busy that they have not had time to design and implement a computer-based system to track their operation. Their first priority is to develop a database management system to track inventory and sales information.
You are hired to design, program, and implement the system. You will be working on elements of this system in the rest of the SLP assignments in this course.
The owners provide you with the following facts:
• The bookstore sells used and hard-to-find books and CDs. Sales are for cash/check or credit card.
• The bookstore mainly caters to the needs of people living in the area.
• The bookstore maintains an inventory of books and CDs.
• Store sales are recorded at a Point-of-Sale (POS) terminal.
• When books are purchased by the store from individuals or organizations, the data are entered into the system.
The owners ask you to:
• Design a database to track inventory and sales information. The database should provide the following information:
1. Current inventory status of books and CDs
2. Sales Transactions List
3. Monthly Profit Report
4. Inventory Status Query by Product
5. Sales Transactions that each employee has processed
6. Any other output you think appropriate
This is the relevant data for the first month of operation. The owners have saved the information in an Excel file. You might want to take them into consideration when you design your database.
Inventory Data
|
Product ID
|
Product Name
|
Unit Price
|
Cost as
% of Price
|
Current
Inventory
|
Monthly
Purchases
|
|
40
|
Little Prince
|
$35
|
50%
|
20
|
3
|
|
42
|
War and Peace
|
$45
|
50%
|
14
|
2
|
|
44
|
The Emperor's New Clothes
|
$40
|
50%
|
12
|
0
|
|
46
|
For Whom the Bell Tolls
|
$35
|
50%
|
10
|
2
|
|
48
|
Pride and Prejudice
|
$25
|
50%
|
5
|
0
|
|
50
|
Pinocchio
|
$35
|
60%
|
5
|
0
|
|
52
|
Canterbury Tales
|
$40
|
60%
|
10
|
0
|
Sales Data
|
Trans ID
|
P.O. Number
|
Product ID
|
Trans Code
|
Date
|
Customer ID
|
Units Sold
|
Cashier
|
|
1
|
393432
|
42
|
2 (credit card)
|
1/10/15
|
4269
|
3
|
Jason
|
|
2
|
234321
|
40
|
2
|
1/11/15
|
8934
|
2
|
Mary
|
|
3
|
557842
|
50
|
2
|
1/12/15
|
5519
|
2
|
Jason
|
|
4
|
337891
|
46
|
1 (check)
|
1/14/15
|
6598
|
3
|
Berry
|
|
5
|
198142
|
48
|
2
|
1/14/15
|
1096
|
2
|
Berry
|
|
6
|
221244
|
40
|
1
|
1/15/15
|
4269
|
1
|
Jason
|
|
7
|
1112
|
40
|
1
|
1/18/15
|
3314
|
2
|
Amy
|
|
8
|
414125677
|
42
|
2
|
1/20/15
|
6539
|
2
|
Jason
|
|
9
|
2343112
|
46
|
2
|
1/24/15
|
4456
|
1
|
Amy
|
|
10
|
343411
|
40
|
1
|
1/28/15
|
5519
|
1
|
Mary
|
SLP Assignment
Please note: The SLP assignment in each module is built around this bookstore project, and each assignment is built on the previous one. By the end of this course, your report will need to satisfy all requirements of this project. In Module 1, your task is to:
• Discuss the type of data needed for such a system (attributes), the data types of these attributes, and data sources (where you might obtain such data, such as Point of Sale).
• Write a 2- page paper explaining how you would design the database system, focusing on the tables, attributes, and primary keys to address the requirements of the store owners. Do not be afraid of not getting it right the first time. You will be able to improve your design over the next few modules after you learn new DB concepts and principles.
To make your report easier to follow, it helps to list the tables you will develop and their associated attributes in the report.
SLP Assignment Expectations
1. Analyze a domain and define data requirements.
2. Discuss how to identify the kinds of data required.
3. Communicate effectively with your audience.
Module 1- Background
Data, Database, Database Management System, and Relational Database Modeling
This module covers the following topics:
1. Why study database?
2. What is database?
3. The dominant design methodology
4. Relational database modeling:tuple and schemata
5. SQL - language for writing database queries
Why study database?
Let's start with the question: Why study databases? As Dr. RasmusPagh of IT University of Copenhagen humorously put it, there are four reasons:
• Academic's reason: Databases touch upon many interesting topics in computer science.
• Programmer's reason: Need to use databases when programming applications.
• Information pilot's reason: Want to work with and extract information from large, changing data sets.
• Capitalist's reason: Everybody needs databases, so there is a lot of money to be made.
What is database?
A database is a shared and integrated computer structure that contains end user data (raw facts) and metadata (data about data). A Database Management System (DBMS) is a system for providing efficient, convenient, and safe storage of and multiuser access to (possibly massive) amounts of persistent data.
All major general purpose DBMSs are based on the so-called relational data model, which means that data is stored in a number of tables with named columns. For example, here's a table called "Books" showing the New York Times best sellers, it is represented with a relational data modeling.
|
Title
|
Author
|
publisher
|
category
|
list_price
|
...
|
|
The Lost Symbol
|
Dan Brown
|
Doubleday Books
|
Fiction
|
29.95
|
|
|
Superfreakonomics
|
Steven Levitt and Stephen Dubner
|
William Morrow
|
Nonfiction
|
29.99
|
|
|
Lego Star Wars
|
Simon Beecroft
|
DK Publishing
|
Children
|
21.99
|
|
|
...
|
...
|
...
|
...
|
|
...
|
Relational data modeling
A relational data model is a data model where all data takes the form of relations. A relation consists of tuples, where a tuple is an ordered list of values.
Tuple
An example of a tuple is:
(The Lost Symbol, Dan Brown, Doubleday Books, Fiction, 29.95)
Each relation has a set of attributes (or columns), with a distinct name within its relation (e.g., title, author, publisher, etc.). Each tuple (or row) in a relation has a value for each attribute. Each attribute has a type (e.g., varchar, float, etc.). Relations are usually written as two-dimensional tables, with the attributes as the first row, and the tuples in the remaining rows. A database contains a set of relations (or tables), each with a distinct name.
Schemata
Schemata are used to describe relations in the relational data model. A schema has two parts:
• The name of the relation
• A tuple with its attributes (plus sometimes also the attribute data types)
• For example, the above relation can have the schema:
• Books (title, author, publisher, category, list_price)
• The actual contents (tuples) of relations are called instances.
A key for a relation is a set of attributes such that no two tuples can have the same values for all of their key attributes. Keys are useful in the following ways:
• Key values identify specific tuples.
• The system may build special indexes over key values.
• Other tuples may use key values as logical "pointers."
• Specify keys by underlining.
Read through the slides for Module, and make sure you understand the core concepts introduced there, including the relational model, table, schema, and key.
In this module, we will download Oracle Express 11g and SQL developer. So what is the connection between these two? SQL developer is a client program that Oracle developers use access Oracle database, and to write and debug their SQL and PL/SQL code. In many ways, SQL developer's function is similar to SQL* Plus. The difference is that SQL developer has a user friendly interface whereas SQL* Plus works in command prompt mode. All the SQL statements you can write using SQL developer can be written using SQL*Plus. You do not need to download SQL*Plus if your computer runs Windows since it is part of the package. To test it, click on Using SQL *Plus.
Dominant design methodology
The dominant design methodology for relational databases consists of three steps:
1. Identify all relevant Entities and Relationships, and describe them using so-called ER model notation.
2. Convert the ER model to a number of relations.
3. Eliminate (or reduce) redundancy by splitting relations. This process is called normalization.
SQL - language for writing database queries
The language for writing database queries is SQL (Structured Query Language, sometimes pronounced "sequel"). For example, the SQL to get the book title written by Dan Brown from the above table is:
SELECT title
FROM Books
https://cdad.trident.edu/Admin/coursedev.aspx?course=392&term=106&mod=1&page=bkgWHERE author='Dan Brown';
The SQL to get all books that cost less than $30 is:
SELECT *
FROM Books
Where list_price< 30;
SQL is based on a mathematical formalism called relational algebra and has the general form of SELECT-FROM-WHERE. We will introduce more SQL in later modules
There are many database management systems in the market (e.g., Access, IBM DB2 Express-C, Apache Derby, MySQL, and Oracle) that are based on relational data modeling. Any of them can be used to learn database. According to Gartner report, Oracle had the No. 1 worldwide RDBMS market share in 2012. Learning Oracle will give students a valuable skill set in the job market. Therefore, Oracle will be the teaching tool in this course. Oracle has a line of database products: Standard edition one, standard edition, enterprise, express edition, and personal edition. The next question is which one to choose? Since Oracle express has all the important features of DBMS, can be downloaded fast and upgraded easily to other products, we will be using Oracle Express 11g in this course.
Read through the slides for Module 1 and get a basic understanding of the core concepts introduced there: data, database, data model, and database management system. We will go into greater detail about these concepts in the rest of this course. (Note: pictures used in the slides for this course are from Peter Rob and Carlos Coronel, Database Systems: Design, Implementation, and Management, published by Thomson Course Technology).
Required Reading
PowerPoint Presentation: Module 1: Data, Database and Database Management Systems
Greenspun, P. (2011). SQL for Web Nerds, Chapter One: Introduction (read until "How does this RDBMS thing work?") https://philip.greenspun.com/sql/introduction.html
Greenspun, P. (2011). SQL for Web Nerds, Chapter Two: Data Modeling. https://philip.greenspun.com/sql/data-modeling.html
Allen, S., & Terry, E. (2005). Beginning Relational Data Modeling. Chapter One: Understanding and Organizing Data: Past and Present. https://books.google.com/books?id=62CFtFea0NsC&printsec=frontcover&source=gbs_v2_summary_r&cad=0#v=onepage&q=&f=false.
Ullman, J., &Widom, J. (n.d.) A First Course in Database Systems, Chapter 1: The World of Database Systems. https://infolab.stanford.edu/~ullman/fcdb/ch1.pdf
Grehan, D. (2005). "When to Use an ODBMS." https://www.odbms.org/introduction-to-odbms/when-to-use-an-odbms/
Obasanjo, D. (2001). "An Exploration of Object Oriented Database System." https://www.25hoursaday.com/WhyArentYouUsingAnOODBMS.html
Attachment:- Assignment.rar