Computer Science: Database differences

Questions:

 

1. The Relational Model: Advance Topics

 

A. In the following exercises use the data in the Premiere Products database shown in Figure 2-1 in Chapter 2.

 

 

  1. Find the order number and order date for every order that contains part number XX34

 

Solution:

 

SELECT OrderNum, OrderDate FROM Orders

where OrderNum IN

( Select OrderNum from OrderLine

Where PartNum like ‘__34’)

 

  1. Find the order number and order date from every order that includes a part located in warehouse number 12.

 

Solution:

 

SELECT OrderNum, OrderDate FROM Orders

where OrderNum IN

( Select OrderNum from OrderLine

Where PartNum IN

(select PartNum from Part

where Warehouse like ’12’))

 

  1. SAME AS #2 but use nested queries.

 

Solution:

 

SELECT OrderNum, OrderDate FROM Orders

where OrderNum IN

( Select OrderNum from OrderLine

Where PartNum IN

(select PartNum from Part

where Warehouse like ’12’))

 

 

 

 

 

 

 

 

  1. Use INTERSECT. List customer number, customer name for every customer who is either represented by sales rep number 345 or who currently has orders on file (CustomerNUM in ORDERS).

 

Solution:

SELECT CustomerNum, CustomerName FROM Customer

Intersect

SELECT CustomerNum, CustomerName FROM Customer

where repNum like ‘345’

Union

SELECT CustomerNum, CustomerName FROM Customer

where CustomerNum IN

(select CustomerNum from Orders)

 

  1. Use MINUS. List customer number, customer name for every customer who is either represented by sales rep number 345 or who does not have orders currently on file.

 

Solution:

 

SELECT CustomerNum, CustomerName FROM Customer

Minus

SELECT CustomerNum, CustomerName FROM Customer

where repNum like ‘345’

Union

SELECT CustomerNum, CustomerName FROM Customer

where CustomerNum IN

(select CustomerNum from Customer

Minus

Select CustomerNum from Orders)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. Normalization

 

A. “Obtain a common document such as a sales slip, customer invoice from an auto repair shop, credit card statement, etc. anything you have will work, make sure personal information is not given or visible. Build your database to include the below information.  ***see note below assignment requirements.

 

 

  1. Use the normalization steps described in this chapter to convert this user view to a set of relations in third normal form.

 

  1. Draw a relational schema.

 

Solution:

 

 

  1. List several integrity rules that you would recommend to ensure the quality of the data in this application”  (Hoffer, Prescott, & Topi, 2009, p. 250).

 

 

****Please submit this Question separately and post what Database format was used: Access, mySQL, etc.

 

Essay Questions. 

 

3. Design Method:

 

“Denormalization can be a controversial topic among database designers. Some believe that any database should be fully normalized. Others look for ways to denormalize to improve processing performance. What is your opinion? Why?” (Hoffer, Prescott, & Topi, 2009, p. 302).

 

Solution:

 

Denormalization is the process of optimizing database performance by adding the redundant data or by grouping the data. We should use denormalization to eliminate the inefficiencies in the database. Using denormalization we can view the objects as one to many relationship as an attribute of one relation. But there are certain risks of using denormalization. Denormalization can only be accomplished with a good knowledge of the database. If performance problems indicate that demoralization is needed then we should use demoralization and must consider the amount of effort needed to demoralize the database and update changes but one of the disadvantage of demoralization is that database update occur more slowly.

 

4. DBMS Functions and Database Administration

 

“Visit some of the Web sites for open-source databases, such as www.postgresql.org and www.mysql.com.

 

What do you see as major differences in administration between open-source databases, such as MySQL, and commercial database products, such as Oracle? How might these differences come into play when choosing a database platform? Summarize the DBA functions of MySQL versus PostgresSQL“(Hoffer, Prescott, & Topi, 2009, p. 612).

 

Solution:

Most of the companies try to recognize and manage the information by using the database systems. Selecting the right relational database management system is difficult especially for those companies which plan to organize their business around it.  Various factors help in deciding whether to select to open source database or commercial database. Cost is the major factor which helps in the selection of database. There are various differences between open source and commercial database.

An open source operating system is acquired at no or low costs but lack in other areas like support as compared to commercial databases.

Commercial products have better support for their products while an open source product does not have such type of support for their products.

Many organizations do not need high availability features and can make use of an open source RDBMS at much lower cost.

Commercial databases provide various features for their products as compared to open source database.

Example of commercial database is Oracle and of open source database is MySQL.

These differences play an important role for selecting the database. For every organization there are various needs and priorities of various organizations. So it cannot be said about the use of particular database, it varies from organization to organization. Two identical organizations can make different choices, the organization deciding to pay for the high availability features will choose commercial database and other who decide to forgo those features will choose an open source product like MySQL.

Mysql is assumed to be the faster and less full featured of the two database systems while PostgreSQL is more densely featured database system often prescribed as an open source version of oracle. PostgreSQL is a unified database server with the single storage engine. MySQL has two layers, an upper SQL layer and a set of storage engine. Most common storage engine in mysql is InnoDB for almost full ACID support and provide high performance on large workloads. PostgreSQL and Mysql both have an impressive array of features that increase data integrity, functionality and performance.  These features included in the database help the database to improve the performance, ease of use and functionality or stability.  Postgresql provide various features like: efficient executor for both static sql, TOAST data compression, improved cache management and huge scalability on write intensive workloads. Mysql supports stored procedures while Postgresql supports stored functions.