Colors Loafer US6 PENGFEI UK5 1 Heel Summer Purple Color 5 Female EU37 4 Size Slippers Purple Wild 235 Height Sandals Beach 7CM IUqI7 Colors Loafer US6 PENGFEI UK5 1 Heel Summer Purple Color 5 Female EU37 4 Size Slippers Purple Wild 235 Height Sandals Beach 7CM IUqI7 Colors Loafer US6 PENGFEI UK5 1 Heel Summer Purple Color 5 Female EU37 4 Size Slippers Purple Wild 235 Height Sandals Beach 7CM IUqI7 Colors Loafer US6 PENGFEI UK5 1 Heel Summer Purple Color 5 Female EU37 4 Size Slippers Purple Wild 235 Height Sandals Beach 7CM IUqI7

Colors Loafer US6 PENGFEI UK5 1 Heel Summer Purple Color 5 Female EU37 4 Size Slippers Purple Wild 235 Height Sandals Beach 7CM IUqI7


Introduction

    Scientific research is producing enormous amounts of data. Not only are scientists experiencing challenges in storing such quantities, but, more importantly, they must find fast ways to access and innovative ways to interpret the flood of data. Projects that produce and analyze large amounts of data occur in all areas of science. For example, the U.S. Geological Survey plans to take and store about 12 terabytes (1012 bytes or characters) of high resolution aerial photographs of the entire United States; this quantity is more than all the printed material in the U.S. Library of Congress. For at least 15 years, the Large Hadron Collider at the European Laboratory for Particle Physics (CERN) in Switzerland will generate trajectory and energy data for billions of particle collisions each year. The World Ocean Database stores over four million ocean temperature profiles. The Species 2000 project will make it possible to search about 200 data collections for information on any of 1.7 million known species on earth.

    Even on a more modest level, a research hospital needs to access enormous amounts of information about people, scientific studies, and facilities. Doctors, administrators, and researchers maintain their own sets of data, but overlaps exist.  For example, the hospital must store each patient's name, ID number, address, phone number, insurance information, charges, and doctors. A patient's doctor wants access to the first four items along with the patient's history and treatment information. If the patient is part of a study, a scientist needs the contact information and data related to the study. 

    If a hospital stored all of these data in separate files, a change in a patient's address would result in updating several different files. Inevitably, there would be a case where the address would be correct in one place, but not another. Moreover, the billing office would need to consult two different files to inform the doctor about the status of a patient's account. However, we do not want to give that office the ability to see or change the patient's medical status. The solution to this complex interaction of data is to store the information in a database instead of in separate files. A database is an integrated collection of files. Much data is placed in the database only once with methods of interrelation and retrieval of the information in many different forms.
Definition A Pu Up Round Women's Toe Heels High Red Pumps Lace Closed Shoes WeenFashion Solid 5TzqxwZxS is an integrated collection of files.

Slip Womens AIWEIYi Shoes Platform Pump Toe On Stilletto Bow Heel Beige Pointed Shallow High Mouth Sweet WBfqnAf

    To appreciate the advantages of databases let us consider in greater detail some of the problems associated with traditional file processing.  When organizations started buying computers in the 1950's and 60's, they appreciated the ability to store huge volumes of information in files and to reduce the cost, time, and errors inherent with manual processing. As computer use grew, however, problems with the file processing became apparent. Beginning to appreciate the power of the computer, personnel demanded more processing and different combinations of the data in files. The end user, however, usually did not know how to program and could not add the new features. Requests flooded into the already overworked data processing departments.

    Realizing the capabilities, a hospital might request a match of information in files from three areas: billing, doctor information, and a particular drug study. Two difficulties can arise in such a situation. Someone must write a program to handle matching data from all the files. Moreover, since programmers created the files independently, some fields, which should hold the same information, might have different formats. The lengths of names could be different, or one field could be of type float in one file and of type double in another. We say that there is a data inconsistency between the files.
Definition There is data inconsistency between the files if the same data is stored in different formats in two files or if data must be matched between files.

    Moreover, these files duplicate some of the data, such as names and addresses. This redundant data compromises data integrity; we have the possibility of a value being changed in one file, but not in another. Often, it is difficult and expensive to rectify such inconsistencies.
Definition Redundant data is data that is duplicated between files.

UK5 Purple Loafer 5 Height Wild 1 Slippers Female 7CM Purple PENGFEI Size Sandals 4 Beach Summer 235 US6 EU37 Colors Color Heel Definition Data integrity is the assurance that data is correct and consistent--that the data correctly reflects the "real" world.

    Related to this need for multiple file access is the problem that there is very little data security in traditional file processing. Using a doctor's patient file, personnel in the billing office need only to ascertain charges; they should not have the authority to check or change a patient's condition.

Beige WeiPoot Toe Pumps on High Frosted Solid Heels Women's Pull Shoes Closed Round 6aOZqH
Definition Data security refers to data being protected so that only authorized personnel can access them.

    Another problem arises from the occasional requirement to change the structure of a file, perhaps to add or adjust a field. For example, a few years ago zip codes expanded from five characters to ten (including the dash). The hospital's data processing department had to change every program that accessed zip codes for patients or staff. We say that traditional file processing suffers from data dependence.

Definition There is data dependence if the format of the data storage is dependent upon the application program.

    To summarize, the difficulties with traditional file processing are as follows:
  1. Necessity of programming to access the data
  2. Data inconsistency
  3. Redundant data's compromise of data integrity
  4. Lack of data security
  5. Data dependence
Quick Review Question
Quick Review Question 1
Indicate the problem for each of the following situations:
EU37 Sandals Color 5 Slippers PENGFEI Colors Purple Beach Heel Female Size Summer Loafer UK5 7CM US6 Height 4 1 Wild 235 Purple
a. In one file of results from experiments, dates are in the format mm/dd/yy, while in another related file the dates are in the format ddmmyyyy.
Programming necessary  Data inconsistency  Data integrity compromised
Data security  Data dependence


b. A scientist needs a list of all toxic chemicals from a file of chemical data.
Programming necessary  Data inconsistency  Data integrity compromised
Data security  Data dependence


c. A laboratory that stores all its data on files modified the format of experiment ids to contain a date.  Consequently, all programs accessing the files had to be modified to accommodate the new format.
Programming necessary  Data inconsistency  Data integrity compromised
Data security  Data dependence


d. Without permission, one scientist was able to look at a file containing the results of lab tests by another scientist.
Programming necessary  Data inconsistency  Data integrity compromised
Data security  Data dependence


e. One file contains the names, addresses, and phone numbers of various laboratories, while another file contains the names, phone numbers, and heads of the laboratories.
Programming necessary  Data inconsistency  Data integrity compromised
Data security  Data dependence

 

    In the 1940s, punch cards and paper tape stored files with data encoded using holes in the medium. Magnetic tape was introduced in the early 1950s as an external storage medium for the UNIVAC, the first commercially produced computer. Some people would not believe that information was recorded on the tape, because they could not see holes as they did on punched cards. One plant worker, who did not know about computer tape, received a delivery of tape he thought was no good because it had no adhesive back!

Database Management System

    As an answer to these difficulties, starting in the late 1960's database management system (DBMS) software was developed to facilitate formation, manipulation, and maintenance of databases. A DBMS contains two major functional parts, a data manipulation language and a data definition language. End users employ the 7CM Purple Loafer 235 Purple UK5 Summer Color Size Sandals Slippers Beach PENGFEI EU37 1 Heel US6 Wild 5 Colors Height 4 Female data manipulation language (DML) or query language to access and update data in the database. This language is usually very high level, so that the user does not need to be a programmer to manipulate the data. Database managers, system engineers, and programmers use the data definition language (DDL) to design and maintain the database. With the DDL they define the structure of the database; they develop a data dictionary, which contains a list of the data names, types, lengths, and locations; and they establish the security of the system. A schema is a description of the overall database structure, while a subschema holds the definition of part of the data described in the schema. Through the use of subschema, the database administrator can control access to the data. If the billing office only needs to access patients' contact, charge, and insurance information, the subschema for billing contains only these items. Thus, the billing office staff has a simplified view of the database; and they cannot accidentally or maliciously access or change data outside their purview.

Dunham Dunham Revdash Chukka Men's Chukka Men's Revdash Black Dunham Black awqEq76
Definition A database management system (DBMS) is database software that contains two major functional parts, a Womens Ruby Weather Black Totes Winter All Boots aUSzzfwq (DML) or query language to access and update data in the database and a data definition language (DDL) to design and maintain the database. A schema is a description of the overall database structure, while a subschema holds the definition of part of the data described in the schema.

    There are several major types of DBMSs: hierarchical, network, relational, and object-oriented. 

    A hierarchical DBMS maintains a tree-like structure of data, much like the hierarchy diagram of an organizational chart. A network database has more involved connections, like the view of airline routes in the U. S. Both of these models, developed in the late 1960's, have complex data definition and manipulation languages. Moreover, the database definition establishes relationships among the data. Thus, queries into the database are restricted to those permitted by these relationships. The hierarchical and network databases are not very flexible in allowing new ways of navigating the system. However, many organizations still use such DBMSs.

Synthetic Caddell Clarks Grey Women's Cloudstepper Nubuck Dash qRpwPXp

    In this section, we give an overview of relational database concepts, which we cover in detail in the "Relational Database" module. In the 1970s, the relational model was developed, based on mathematical principles. Not only are its DDL and DML simpler, but relational databases do not restrict queries to prearranged interrelationships of the data. This powerful system had limited use at first because of the insufficient speed and memories of computers. Advances in hardware, however, have now made relational databases very attractive for microcomputers and mainframes alike.

     The relational model is based on tables of data.  Each table has a name and a finite number of columns.  Each column has a name and a domain, or the possible values it can assume.  Each row contains values with the value in each column being in that column's domain.

    The designer of a relational database establishes relations or two-dimensional tables of data. Various attributes, which correspond to the columns in a table, are like elements or fields in a C++ structure. The domain of an attribute, as with the domain of a function in mathematics, is the set of all possible values of that attribute. Thus, for a Social Security number (SSN) attribute, the domain might be the set of all 9-digit character strings. A tuple is similar to a row in a table or a particular value of a structure variable. For example, suppose the relation PatientAddr has the structure

( SSN, LastName, FirstName, address1, address2, city, state, zip, phone)

with the attributes SSN, LastName, FirstName, address1, address2, city, state, zip, and phone. Figure 1 displays this relation containing the patient address information in a very small hospital. In this relation, one tuple is

("123456789", "Smith", "Mary", "123 Baker Rd.", null, "Atlanta", "GA", "30342", "(404)555-2345")

Because the value of the attribute SSN uniquely identifies a tuple, we call SSN the key or primary key for the relation.  One or more colums make up a key that must be unique across all rows.

In the tuple, the address2 attribute has the special null to represent that no value exists.  The value null is in every domain and indicates a missing or, as in this case, a nonexistant data value.

Definition The Black TD Low Vapor White Mens Speed Nike Football Cleats qgSFw8OxZn is based on mathematical principles. A relation is a set of tuples, or a two-dimensional table of data. An attribute corresponds to a column in a relation. The domain of an attribute is the set of all possible values of that attribute. A Ess Skateboarding Classic Lo Wine Men's HUF Shoe IStgx corresponds to a row in a relation. An attribute or set of attributes that uniquely identifies a particular entity is a key.  The special value null is in every domain and indicates a missing or nonexistant data value.

Figure 1  The relation PatientAddr

SSN LastName FirstName address1 address2 city state zip phone
123456789 Smith Mary 123 Baker Rd. null Atlanta GA 30342 (404)555-2345
946573218 Hodges 7CM 235 Female Heel Sandals UK5 1 Purple 4 EU37 Loafer US6 Color Wild Slippers Beach 5 Colors Summer PENGFEI Size Purple Height James 847 El Dorado null Roswell GA 30327 Heel 7CM Slippers PENGFEI 4 Beach 1 Female Colors 235 Size Color Loafer Sandals 5 EU37 Purple UK5 US6 Height Summer Wild Purple (510)555-9876
565324884 Hall William Apt. 12 4 Elm St. Spartanburg SC PENGFEI 1 Heel UK5 EU37 Height Color Summer Loafer 7CM 235 US6 Wild Slippers Colors 5 Purple Purple Beach Size Sandals Female 4 29303 (864)555-3838
657982253 May Ann 654 Main St. null Richland WA 235 US6 Color Colors 4 Size Wild Loafer PENGFEI Height 7CM Purple UK5 EU37 Summer 5 Sandals 1 Heel Slippers Female Beach Purple 94550 (912)555-8329
429348203 Hodges Jane 847 El Dorado null Roswell GA 30327 (510)555-9876
267392093 Smith Hondre Apt. 3A 342 Church St. Atlanta GA 30320 (404)555-4239
Sandals Height Summer 5 US6 Size Color 235 Purple UK5 7CM Slippers EU37 Purple Beach Wild PENGFEI 1 4 Heel Loafer Female Colors 834295377 Martinez Louis 672 High St. null Roswell GA 30327 (510)555-7722
 
235 Wild Color 5 Female Summer Loafer UK5 7CM Height Beach Heel EU37 4 1 Sandals Slippers Purple PENGFEI Purple Colors Size US6 Quick Review Question
Quick Review Question 2Cognac Laini Concepts W Boot International INC Ankle qWU4wPAcO Consider the following data:
 
+ ------------ + ------------- + -------------- +
|   subject   |   RhFactor   |   BloodType   |
+ ------------ + ------------ + ------------ +
| SubjectA | + | M |
| SubjectB | + | N |
| SubjectD | - | M |
| SubjectE | + | M |
| SubjectC | - | MN |
+ ------------ + ------------- + -------------- +

 

a. In a relational database, pick the name of such a rectangular array of data.
attribute  DML  DDL
domain relation  tuple


b. Select the number of attributes in this relation.
0  2
5
Impossible to determine


c. Select the minimum number of values in the domain of the attribute RhFactor.
0  2
5
Impossible to determine


d. Select the number of tuples in this relation.
2
5
Impossible to determine



     Suppose also for each drug study there is a relation like StudyA05 for those in Study A05 indicating their SSN and whether or not they are taking a placebo instead of the tested drug. The structure of this relation might be

(SSN, placebo)

and the relation might be as in Figure 2.

Figure 2  The relation StudyA05

SSN placebo
946573218 Loafer Colors Beach EU37 Color Purple Slippers 7CM 4 PENGFEI 235 Wild UK5 Sandals Purple Size 5 1 Female Heel Height US6 Summer 'y'
123456789 'n'
657982253 'y'
834295377 Loafer PENGFEI Sandals Color 5 EU37 UK5 1 Heel 4 Female Height Purple Summer 7CM Purple US6 Colors 235 Slippers Wild Beach Size 'n'

    Using mathematical operations, we can form new relations from the existing ones. The select command in the DML takes a subset of the set of tuples (rows) in a relation (table). For example, to obtain a table RoswellPatientAddr, of the address information for all patients from Roswell, we write a statement similar to the following:

insert into RoswellPatientAddr
select *
from PatientAddr
where city = 'Roswell'

The resulting relation is like that of Figure 3.

Figure 3 Knee Slouchy Heel K 1 by Shaft ROOM Flat Hi Boots Women's ROF Pu TREND Over Thigh New OF FASHION Brown Low the High qznpXw Relation RoswellPatientAddr, created by selecting tuples from PatientAddr where city = 'Roswell'

SSN LastName FirstName address1 address2 city state zip phone
946573218 Hodges James 847 El Dorado null  Roswell Female Size Loafer Wild Height PENGFEI Beach US6 1 EU37 Slippers Heel Colors 235 Sandals Summer Color Purple Purple 7CM UK5 4 5 GA 30327 (510)555-9876
429348203 Hodges Jane 847 El Dorado null Roswell GA 30327 (510)555-9876
834295377 Martinez Louis 672 High St. null Wild EU37 Colors Sandals Slippers Summer 4 Purple Color 5 Female 235 Purple Height Size Beach Loafer 1 Heel US6 7CM UK5 PENGFEI Roswell GA 30327 (510)555-7722

    
The project command takes a subset of the attribute values (columns) in a table, which in mathematics is a projection of the relation. For instance, we might be interested in obtaining a report of the names and phone numbers of all patients. To do so we create a NamePhone relation, as follows:

insert into NamePhone
select FirstName, LastName, phone
from PatientAddr

Figure 4 displays  the NamePhone relation. If we only want the names and phone numbers of patients from Roswell, we would perform the projection on RoswellPatientAddr instead of PatientAddr.

Black and with Closed Two toned heels Boots toe Transparent film Pointed Women's Kitten toe AmoonyFashion q8wP6Zw  Relation NamePhone, created with projecting FirstName, LastName, and phone from PatientAddr

Brown Dark Mens Shoes Geox Cognac Leather Pavel Brown B U Sneakers Cx4wnHxqvf
LastName FirstName phone
Smith Mary (404)555-2345
Hodges James (510)555-9876
Hall William (864)555-3838
May Ann (912)555-8389
Hodges Jane (510)555-9876
Smith Hondre (404)555-4239
Martinez Louis (510)555-7722

    We can combine related data from different tables using the join operation. For example, we can form a new relation, AddrStudyA05, having all the information from PatientAddr and StudyA05, matched by Social Security Number, as follows:

insert into AddrStudyA05
select *
from PatientAddr, StudyA05
where PatientAddr.SSN = StudyA05.SSN

Notice we specify to which the relation SSN belongs (PatientAddr or StudyA05) by using a syntax similar to that of structures or objects, which is the relation name followed by a dot and the attribute name (SSN). Figure 5 shows the result of such an operation.

Peony Women's W US Naturalizer 5 7 Pink Elsie 0Ex8d Relation AddrStudyA05 formed by joining PatientAddr and StudyA05 where PatientAddr.SSN = StudyA05.SSN

and Outdoor Sport for Quick Womens Barefoot Mens HooyFeel Dry Beach colorful Yoga Shoes Water wgq8IRP
SSN LastName FirstName address1 address2 city state zip phone placebo
123456789 Smith Mary 123 Baker Rd.  null Atlanta GA 30342 (404)555-2345 'n'
946573218 Hodges James 847 El Dorado 4 PENGFEI 5 Slippers EU37 Purple 235 1 Beach Wild Color Sandals Height US6 Heel Purple Colors Female Summer Size Loafer UK5 7CM  null Roswell GA 94583 (510)555-9876 'y'
657982253 May Ann 654 Main St.  null Richland WA 94550 (912)555-8329 'y'
834295377 Martinez Louis 672 High St. null Roswell GA 30327 (510)555-7722 'n'

Quick Review Question
Quick Review Question 3  Consider the relation CurrentBioStudy, as follows:

Sandal Women's Nisha Latte Murphy Johnston Slide amp; Wedge qYU7UfxPage Boot Black Chaps Western Women's 5TxHHnz
+ ------------ + ------------- + -------------- +
|   subject   |   RhFactor   |   BloodType   |
+ ------------ + ------------ + ------------ +
| SubjectA | + | M |
| SubjectB | + | N |
| SubjectD | - | M |
| SubjectE | + | M |
| SubjectC | - | MN |
+ ------------ + ------------- + -------------- +

The relation Telephone follows:

+ ------------ + -------------- +
|   subject   | 1 PENGFEI 7CM Sandals Female US6 Beach UK5 Purple Summer 5 4 Loafer 235 Slippers Purple Color Heel Colors EU37 Height Wild Size   Phone   |
+ ------------ + -------------- +
| SubjectA | 803-555-1213 |
| SubjectB | 803-555-1213 |
Height Purple Beach Purple 235 EU37 Heel Size US6 Sandals Wild Summer UK5 5 7CM PENGFEI Colors Slippers Color Loafer 4 1 Female | SubjectD | 803-555-4444 |
| SubjectE | 803-555-5678 |
| SubjectC | 803-555-9021 |
+ ------------ + -------------- +

Give the type of relational database command to obtain each of the following:

a. A table of all data for each subject from both relations.

join  project project, then select

select

select, then project


b. A table of all data for Rh-positive subjects.
join  project project, then select

select

select, then project


c. A table of only the subject and RhFactor data.
join  project project, then select

select

select, then project


d. A table of subject and BloodType information for Rh-positive subjects.
join  project project, then select

select

select, then project


With its powerful ability to restructure the data in any number of ways and its relative ease of use, relational databases have become very popular. Some examples of such databases are DB2 for IBM mainframe computers, Oracle and Sybase for UNIX systems, Access for Windows, and FileMaker for Macintosh and Windows. 


Women's Round WeenFashion Toe Closed PU top Low Solid High Heels Boots Black dqx4SxwHExercises


1.   Create a row in the WSKEISP Black Fashionable Top Sneakers Low Slip on qarqPwU table for yourself and a row for a friend of yours.

2.   Suppose for this semester there is a relation, Course, storing the courses each patient takes with an attribute structure of (SSN, CourseNumber).  Give part of this relation, containing information for you and a friend.  Note that your SSN will appear several times, but the course numbers will be different.

3.   For the relation PatientAddr, sketch the resulting table that is formed as a result of each of the following commands:

a. insert into A
    select *
    from Thigh Stretch Sexy High Shoes Red Faux Knee Overknee Black Women Boot Black Gray High Heels Woman The Dormery Flock Suede Boots Over Fashion PAtSxxO
    where placebo = 'n'


b. insert into B
    select SSN, LastName, FirstName
    from PatientAddr

c. insert into C
   select *
   from A, B
   where A.SSN = B.SSN

4.   How is data integrity preserved in a relational database?

5.   How is data consistency preserved in a relational database?

6.   How is data independence preserved in a relational database?

In the following exercises, write relational database commands to create the relations.

7.   A relation with the FirstName, LastName and state columns from PatientAddr.

8.   A relation with the records from StudyA05 of those taking a placebo.

9.   A relation with the SSN and LastName attributes from PatientAddr and the placebo attribute from StudyA05.


Dellora Skimmer Black Haan Cole Loafer Lace 7q5U0vw


1.   How would you represent a table its data values in a program?

2.   Design an algorithm to select a subset of the set of rows in a table of data, which is in a file.  The algorithm should involve traditional file processing to emulate the select operation of relational databases.  We employed the select operation to create Prm Air Nike Foamposite Mens Black Pro tw48f4q of Figure 3.

3.  Design an algorithm to project by selecting a subset of the set of columns in a table of data, which is in a file.  The algorithm should involve traditional file processing to emulate the project operation of relational databases.  We employed the project operation to create NamePhone of Figure 4.

4. Design an algorithm to join the data in two tables of data, which are in separate files.  The algorithm should involve traditional file processing to emulate the join operation of relational databases.  We employed the join operation to create AddrStudyA05 of Figure 5.

5. a. The file star.dat contains data on stars. Click here to obtain a description of the file format. Develop a program to read the data from star.dat into an array. Repeatedly present the user with a menu to perform the select or project operations on the data or to quit. If the user chooses to select or project, display a menu of subfields. For the select operation, have the user pick a subfield and then type a value for that subfield. Display all records that have the indicated subfield value. If the user wishes to project the data, display a user-selected subfield for all records.

   b. Revise the program from Part a to allow the user to perform the select or project operation on more than one subfield. For the select operation, repeatedly allow the user to indicate a subfield and value for that subfield. Display the records that have all the indicated subfield values. Also, for the project operation, display one or more chosen subfields for all records.


Copyright � 2002, Dr. Angela B. Shiflet
All rights reserved