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.
Q2) Create XSL style sheets for each of the following to display in a table format:
Q2a) All employee records and data
Q2b) All employee records whose salary is greater than 40,000
Q2c) Sort employee records by their name and display records
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.
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:
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:
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:
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:
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:
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 ...