Speaking Notes
SSCI 2402
February 25, 2008
Dr. Sweet-Holp, Course Instructor
Dr. Neubauer, Guest Speaker
DATABASES ARE THE FOUNDATION OF MANAGEMENT INFORMATION SYSTEMS (MIS) AND KNOWLEDGE MANAGEMENT (KM) SYSTEMS, INCLUDING DATA WAREHOUSES.
OLTP systems support business processes
OLAP system support strategic decision making
Problems with the "flat file" approach (also called file processing systems) to databases.
- underlying problem is (unnecessary) redundant storage of the same data multiple times
- waste of data entry time
- waste of storage space
- waste of CPU cycles every time the database is backed up
- internal inconsistency
- update anomalies (one change has to be made in many places)
- deletion anomalies (you delete something and loose other things as a consequence)
- if you have multiple applications sharing same data, a change in the data structure will require all the applications to be modified
Example of a flat file "database:"
|
Invoice Number |
Invoice Date |
Sales Person Name |
Sales Person Phone |
Produce Number |
Product Name |
Customer Name |
Shipper Name |
|
114 |
12/19/2003 |
Alice Smith |
222-2323 |
84 |
gyro |
Ben Nice |
Allen Lines |
|
114 |
12/19/2003 |
Alice Smith |
222-2323 |
88 |
nacho |
Ben Nice |
Trucks R Us |
|
115 |
12/22/2003 |
Jim Hill |
123-9898 |
84 |
gyro |
Jack Miles |
Allen Lines |
The better approach is to have some specialized software "between" applications and data and let the programmer just "write to the interface" of that specialized software. That software is known as a DBMS (database management system). Examples are Oracle, Access, MySQL, and SQL Server.
Relational databases were discovered by Dr. E. F. Codd in about 1970.
Instead of one big flat file there are many tables, and relationships between some of them.
An ERD (entity-relationship diagram) is a visual model of either a business environment or an existing relational database.
An analysis ERD is a reflection of the business domain. "Many to many" relationships are okay.
A design ERD is increasingly a representation of the relational database. "Many to many" relationships cannot be implemented directly. Decisions must be made about normalization, indexes, security, and other considerations.
Two approaches to designing a database:
- work from the specifications provided and do as little as possible to create a functional database
- model the business environment -- plan for extensibility
CLASS EXERCISE
Identify prospective entities in the following domain description and draw a preliminary analysis ERD using Chen notation or a similar common notation. You are beginning to design a database to support one or more applications to automate one or more business processes at East Apartment Complex. Document your assumptions, but don't just solve all your problems by making simple assumptions. Identify needs to get additional information about the business domain you are modeling.
East Apartment Complex includes five buildings which contain a total of 42 apartments. A tenet is a person who signs a lease for one or more apartments and is responsible to pay the rent and be responsible for the condition of the apartment. If a tenet has a pet he or she is required to register the pet. Tenets are also required to register their vehicles. A tenet can have only one pet but can register several vehicles. A tenet can also lease a garage, but is not required to do so. If two or more roommates share an apartment each one of them is required to sign the lease as a tenet. Some apartments are furnished and others are not furnished.
MORE THINGS TO KNOW ABOUT RELATIONAL DATABASES
The purpose of a primary key field is to be the unique identifier of rows in that table.
The purpose of a foreign key field is to hold relationships with rows in another table. The value of a primary key "points back" to a row in another table.
The foreign key field always goes on the "many" side of a one-to-many relationship between two entities.
SQL (structured query language) is the way database professionals write their queries to be answered by a relational database management system.
Each entity becomes a table in the relational database.
A Primary Key is used to assure that each row is unique.
The purpose of Foreign Keys is to hold relationships between rows in each of two tables.
The foreign key field ALWAYS GOES ON THE "MANY" SIDE OF A one-to-many relationship.
Please use this resource to practice your SQL queries.
http://www.robertcat.net/callingallpets/default.aspx
An example of object notation is pet.species, where "pet" is the table name and "species" is the column name. What are the likely VALUES of species?
An SQL query is a specification of a virtual table you want back as the answer to the question.
SELECT -- specifies the columns you want back.
FROM -- has to do with the tables necessary and how their are joined together in the relational database
WHERE -- the CRITERIA -- specifies the rows you want back from the query
1) How many columns will there be in the table produced by the following query?
SELECT patient.name, patient.city, patient.state
FROM patient
WHERE patient.state = "GA"
2) How many base tables are required to process the following query?
SELECT patient.name, patient.city, patient.state
FROM patient
WHERE patient.state = "GA"
3) What is the criteria in the following query?
SELECT patient.name, patient.city, patient.state
FROM patient
WHERE patient.state = "GA"
Owner
|
OwnerId |
Name |
Telephone |
|
1 |
Suzanne |
555-1234 |
|
2 |
Henry |
123-5555 |
|
3 |
Alice |
125-1234 |
Pet
|
PetId |
OwnerId (fk) |
Name |
Species |
Weight |
|
1 |
2 |
Rover |
dog |
15 |
|
2 |
3 |
Daisy |
dog |
12 |
|
3 |
1 |
Bob |
cat |
10 |
|
4 |
2 |
Fido |
dog |
12 |
|
5 |
1 |
Fuzzy |
cat |
8 |
The reason the foreign key goes into the "many" side of the relationship is that you cannot force multiple values into one cell of a table.
Please use this resource to practice your SQL queries.
http://www.robertcat.net/callingallpets/default.aspx