BU 5010 Database Appl. For Managers                                    C. Schweikert

Final                                                                                                Spring 2006

Suyun Chen

 

Q1) Create an XML document for an employee database. Include tags or attributes for name, id, salary, and department.

XML

Q2) Create XSL style sheets for each of the following to display in a table format:

     Q2a) All employee records and data

Style Sheet

All Employee


Q2b) All employee records whose salary is greater than 40,000

Style Sheet

Salary is greater


Q2c) Sort employee records by their name and display records

Style Sheet

Sort by Name

Q3) For one employee in your XML document, use valid and validity tags to timestamp the record and create a new version for the employee record.

 

Valid Time

Q4) Give a brief definition or example for each of the following database concepts:

            Q4a) DBA

A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include:

Although not strictly part of a database administrator's duties, logical and physical design of databases is sometimes part of the job. These functions are traditionally thought of as being the duties of a database analyst or database designer

 

            Q4b) Relational Database and ER Diagram 

 A database is an organized collection of information. Common examples of a database would be a telephone book, mailing list, recipe book, or a check book.

The term database is a little different in Access. An Access database refers to a set of data related to a specific purpose or topic, along with the tools needed to utilize and manipulate that data, such as sorting, extracting, or summarizing.

Relational Database Concepts
Prior to understanding the concept of a Relational Database you should first understand the concept of a Flat File Database. A spreadsheet would be considered a Flat File database.

Let's use a mail-order Book company for example. If using a Flat File database one item ordered would equal one record:

Image of one record along a row with columns marked per field.

No problem, right? But what happens if the customer orders several books?
In a flat file database the result would be multiple records and the majority of the fields would contain duplicate data values:

Image of one order for a company, showing the company name, order number and order date repeated several times for each book on that one order.

This is not an efficiently designed database!

Now let's look at the same data stored in a relational database. An Access relational database is comprised of multiple tables each pertaining to a specific topic:

Image showing a customer info table, an order info table and a book inventory table.

Another key piece to the relational database concept is each table contains a field, or a combination of fields, in which the data value uniquely identifies the record and Access will ensure that the data values remain unique to each record. This field is referred to as the Primary Key. A Customer Id field would be added to the Customers table and Book Id would be added to Book Inventory table:

Image showing the same three tables as in the above image, with the Cutomer ID highlighted in the customer table and the Book ID highlighted in the book table.

If each customer is assigned a unique value—their Customer Id—which is then referenced when placing an order. The same would be true for Book Inventory. Each Book is assigned a unique value that is referenced in the Orders table when a book is purchased:

Image showing the customer number referenced in the order table, as well as the book id referenced in the same order record in the order table.

Thus the tables are related to each other by a common field.

A table that contains the "parent" or "primary" information can be linked to the appropriate row(s) in a table that contains "child" or "related" information based on common key field of the two tables.

Relationships
Each relationship will have a Primary (parent) table and a Related (child) table as previously described.

An easy way to determine the Primary table in the relationship is to note the Primary key. Typically the Primary table is the table that holds the Primary key field in the relationship.

In the above image, the Customers table is the Primary table and the Orders table would be the Related table.

How each table is related to each other is another key concept in a relational database.

There are two main types of Relationships: One-to-One and One-to-Many. There is a third relationship type called a Many-to-Many relationship, but I'll cover that type of relationship in a future article.

An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. ER diagrams often use symbols to represent three different types of information. Boxes are commonly used to represent entities. Diamonds are normally used to represent relationships and ovals are used to represent attributes.

 

 

 

            Q4c) Object Oriented Database

A database system that incorates all the important object-oriented concepts.

* Unique Object identifiers

*Persistent object handling

Schema design and query optimization in Object-Oriented Databases can be substantially improved by taxonomic reasoning techniques. A group of researchers is working on these topics at the "Centro di Studio per l'Interazione Operatore-Calcolatore" (CIOC-CNR), Bologna, Italy.

The organization of classes (types) in an inheritance taxonomy to describe an application domain constitutes a basic modelling principle in the database area and in artificial intelligence. In the database area, the class taxonomy is built by the designer and only a few systems guarantee its consistency.

 

This example uses the ODMG C++ Binding and the ODMG Object Query Language (OQL). Other C++ bindings and query languages for object databases would be similar. Notice that all the code is C++, with the exception of the query string. You will not find a database sub-language in this example. The part in blue relates to the animation of transparent persistence (new window). More information on the ODMG specification (new window). Note that the same code would used for object-relational mapping products (new window). Transparent persistent interfaces work with both object database or object-relational mapping products.

d_Database db;

d_Transaction txn;

try {

    db.open("addressDB");

    txn.begin();

    // perform query

     d_OQL_Query query(

         "select x from Person x where x.name = \"Doug Barry\"");

    d_Bag<d_Ref<Person> >   allDougBarrys;

    d_oql_execute(query, allDougBarrys);

    d_Iterator<d_Ref<Person> > iter = allDougBarrys.create_iterator();

    // iterate over the results

    d_Ref<Person> p;

    while( iter.next(p) ){

       // do some addition processing on the person (now shown)

       // now traverse to the address object and update its value

       p->address.street = "13504 4th Avenue South";

    }

    txn.commit();

    db.close();

}

//exception handling would go here ...

 

1