| Plopping data into tables haphazardly is a guaranteed way to create consistency problems and limit what can be extracted from the data base. Good database design is a complex subject, but we can learn the basics |
| A database is a set of tables | |
| The tables are sets of records known as tuples composed of fields each having values of from some primitive data type | |
| The structure of a data base is called a database schema | |||
| The schema specifies … | |||
| The list of table names forming the database | |||
| For each table, the fields of its records | |||
| For each field, its attributes or properties, i.e. data type, key or not key, default value, etc. | |||
| A database as the word is normally used, i.e. tables with specific contents, is known as a database instance (of a data base schema) | |||
| There can be many instances of a single database schema | |||
| Suppose a college wants a database of their students, faculty, courses taught, student transcripts, and so forth, what things should go into a design and how should it be organized? | ||
| Students: first name, last name, home address, transcript … | ||
| Faculty: first name, last name, SS#, home address, rank … | ||
| Courses: class name, number, students attending, grades … | ||
| Deciding on the schema is called “database design” and it takes a little study to do right … but it’s easy to see the principles in action | ||
| Consider the Students & Classes DB from Access |
| Goal: Avoid redundancy |
Consider A Student’s List Of Classes
| One table -- Students And Classes -- contains records that associate students with classes | |
| By listing all records with FIT100’s ClassID, a table is created of the students in FIT100 by StudentID | |
| By looking up each student using StudentID, the other fields of the class list can be located |
| Associating a student with a class is the logical idea behind registering for a class, so Students & Classes corresponds to a real phenomenon -- a plus | |
| Having classes listed in the student record violates the goal of a fixed length record, and makes it cumbersome to create a class list -- minuses | |
| Having students listed in the class record violates the fixed length record goal, and makes it cumbersome to create a registration list for each student -- minus | |
| “Registering students” -- what STAR does -- can be done without touching either Students or Classes tables -- a plus |
| Many tables will be of interest on their own, too |