ACC 3202 ch13

00 4 6000000 jim wilson 333445555 1992 02 01 m 2500000

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 25000.00, (’John’, ’Anderson’, ’444556666’, ’1991-10-31’, ’M’, 20000.00, 4); Chapter 13-32 DATABASE SYSTEMS SQL format for DELETE DELETE FROM departments WHERE dept = 4; SQL format for UPDATE UPDATE departments SET dept = 4 WHERE UPDATE dept = 3; dept Chapter 13-33 RELATIONAL DATABASES The The relational data model represents relational everything in the database as being stored in the forms of tables (aka, relations). relations Chapter 13-34 STUDENTS Student ID Last Name First Name Phone No. Advisor No. 333-33-3333 Simpson Alice 333-3333 1418 111-11-1111 Sanders Ned 444-4444 1418 123-45-6789 Moore Artie 555-5555 1503 ADVISORS Advisor No. Last Name First Name Office No. 1418 Howard Glen 420 1419 Melton Amy 316 1503 Zhang Xi 202 1506 Radowski J.D. 203 A foreign key is an attribute in one table that is a primary key in another table. Chapter 13-35 RELATIONAL DATABASES Alternatives One One for storing data possible alternate approach would be to store all data in one uniform table. store For example, instead of separate tables for For students and classes, we could store all data in one table and have a separate line for each student x class combination. student Chapter 13-36 Last Name First Name 333-33-3333 Simpson Alice 333-3333 ACCT-3603 1M 9:00 AM 333-33-3333 Simpson Alice 333-3333 FIN-3213 3 Th 11:00 AM 333-33-3333 Simpson Alice 333-3333 MGMT-3021 11 Th 12:00 PM 111-11-1111 Sanders Ned 444-4444 ACCT-3433 2T 10:00 AM 111-11-1111 Sanders Ned 444-4444 MGMT-3021 5W 8:00 AM 111-11-1111 Sanders Ned 444-4444 ANSI-1422 7F 9:00 AM 123-45-6789 Moore Artie 555-5555 ACCT-3433 2T 10:00 AM 123-45-6789 Moore Artie 555-5555 FIN-3213 3 Th 11:00 AM Student ID Phone No. Course No. Section Day Time • Using the suggested approach, a student taking three classes would need three rows in the table. • In the above, simplified example, a number of problems arise. Chapter 13-37 Last Name First Name 333-33-3333 Simpson Alice 333-3333 ACCT-3603 1M 9:00 AM 333-33-3333 Simpson Alice 333-3333 FIN-3213 3 Th 11:00 AM 333-33-3333 Simpson Alice 333-3333 MGMT-3021 11 Th 12:00 PM 111-11-1111 Sanders Ned 444-4444 ACCT-3433 2T 10:00 AM 111-11-1111 Sanders Ned 444-4444 MGMT-3021 5W 8:00 AM 111-11-1111 Sanders Ned 444-4444 ANSI-1422 7F 9:00 AM 123-45-6789 Moore Artie 555-5555 ACCT-3433 2T 10:00 AM 123-45-6789 Moore Artie 555-5555 FIN-3213 3 Th 11:00 AM Student ID Phone No. Course No. Section Day Time • Suppose Alice Simpson changes her phone number. You need to make the change in three places. If you fail to change it in all three places or change it incorrectly in one place, then the records for Alice will be inconsistent. • This problem is referred to as an update anomaly. Chapter 13-38 Last Name First Name 333-33-3333 Simpson Alice 333-3333 ACCT-3603 1M 9:00 AM 333-33-3333 Simpson Alice 333-3333 FIN-3213 3 Th 11:00 AM 333-33-3333 Simpson Alice 333-3333 MGMT-3021 11 Th 12:00 PM 111-11-1111 Sanders Ned 444-4444 ACCT-3433 2T 10:00 AM 111-11-1111 Sanders Ned 444-4444 MGMT-3021 5W 8:00 AM 111-11-1111 Sanders Ned 444-4444 ANSI-1422 7F 9:00 AM 123-45-6789 Moore Artie 555-5555 ACCT-3433 2T 10:00 AM 123-45-6789 Moore Artie 555-5555 FIN-3213 3 Th 11:00 AM Student ID Phone No. Course No. Section Day Time • What happens if you have a new student to add, but he hasn’t signed up for any courses yet? • Or what if there is a new class to add, but there are no students enrolled in it yet? In either case, the record will be partially blank. • This pro...
View Full Document

Ask a homework question - tutors are online