Homework

Module 1 - Case

Data, Database, Database Management System, and Relational Database Modeling

Case Assignment

Case assignment in this module consists of two parts:

Part A:

After you've read through the articles and related materials, think about their content carefully and compose a short (3- page) paper on the topic:

An Overview of Database and Database Management System

Part A Assignment Expectations

You should include the following topics in your discussion, not necessarily in the same order:

• What is a database? And how does it differ from a file system?
• What is a database management system? And why is it needed?
• What are the advantages of DBMS?
• What are the advantages and disadvantages of RDBMS and ODBMS?
• What issues should be considered when choosing a database and a DBMS?
• What are the roles of a database and a DBMS for an organization's information system?

Part B:

You will install Oracle database 11g express edition and SQL developer to your computer, validate your installation, and write a short 3--page paper with screenshots of your download. To work on this assignment, you will need to first reading the following materials:

• Oracle Express Installation Guide (2011).
• Oracle Express Getting Started Guide (2011).

And then download SQL developer at http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. Make sure you download the zip filethat includes the JDK1.6.0_35.

In the end, learn how to connect to Oracle database using SQL developer.

Alternative assignment

For students who are not able to download software due to access restrictions:

Write a 2- topage paper (excluding cover page and references) comparing features and characteristics of various types of DBMS (Oracle, Access, and MySQL).

Part B Assignment Expectations

• Successfully install Oracle database 11g express edition and Oracle SQL developer to your computer.
• Describe your experience with the installation.
• Open the sample database HR, make screen shots of the database, and copy them into your paper.
• Open the HR database, describe what information you see there using the terms introduced in this module, such as table, schema and key.

Describe the tables in the HR database.

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.

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?") http://philip.greenspun.com/sql/introduction.html

Greenspun, P. (2011). SQL for Web Nerds, Chapter Two: Data Modeling. http://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. http://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. http://infolab.stanford.edu/~ullman/fcdb/ch1.pdf

Grehan, D. (2005). "When to Use an ODBMS." http://www.odbms.org/introduction-to-odbms/when-to-use-an-odbms/

Obasanjo, D. (2001). "An Exploration of Object Oriented Database System." http://www.25hoursaday.com/WhyArentYouUsingAnOODBMS.html

Attachment:- Assignment.rar