File and Database Design

1. File and Database Design Class 22

2. File and database design: • 1. Choosing the storage format for each attribute from the logical data model. • 2. Grouping attributes from the logical data model into physical records. • 3. Arranging related records in secondary memory so that individual and groups of records can be sorted, retrieved, and updated rapidly. • 4. Selecting media and structures for storing data to make access more efficient.

3. What we’re looking at • Fields, Records, Files, Databases

4. Fields • Name • Data type • Primary key(s) • Data Integrity • Handling missing data • Ownership

5. Data Integrity • Default Value • Picture Control • Range Controls • Referential Integrity • Null Value Control

6. Records • A group of fields stored in adjacent memory locations and retrieved together as a unit. • Denormalization (which you’ll learn in the Database class) will be done on records.

7. Files • Different types of files: • Master File • Look-up Table file • Transaction file • Audit Trail • History files

8. 2 File Organization Types • Sequential • Indexed

9. What to consider in choosing file organization • Fast data retrieval • Efficient use of storage space • Protection from failures or data loss • Accommodating growth • Security from unauthorized use

10. Designing a relational database • Create a table (file) for each entity type. • Choose a primary key for each table. • Create new tables to represent many-to-many relationships. • Add foreign keys to represent one-to-many relationships. • Define referential integrity constraints. • Evaluate schema quality and make necessary improvements. • Choose appropriate data types and value restrictions for each field.