100 likes | 182 Views
Session Objectives # 21 . Key Words. Key Words. Data Redundancy. Entity. Validation. ERD. Primary Key. Foreign Key. Appropriate use of validation.
E N D
Key Words Key Words Data Redundancy Entity Validation ERD Primary Key Foreign Key
Appropriate use of validation Scenario: You will create a flat file database to hold data about students their courses of study. The student table will have the following field names: Student_ID Surname Forename Subject Predicted Grade What would be a suitable data type and/or validation method for each field?? Database developers ask these questions before building the database. They put the information in a simple table called a data dictionary.
Data Dictionary The data dictionary ensures the completeness and accuracy of the database and avoids complications in the development stage Details included in data dictionary are: Field name – which will be used to contain the data. The data type required for that field The size of the data (storage requirement) Any validation that could be/should be used Identify which field will be used as a Primary Key TASK: Create a data dictionary table for the school system using the above bullets as column headings
Relational Databases You will notice from the flat file database a lot of values are repeated. This is known as redundantas it is not needed and only uses up more storage space. By creating another table and linking them together this repetition or redundancy can be avoided. A database of more than one table linked or related to each other is known as a relational database. Entities – Each table in a relational database contains data about one entity i.e you could have one entity (table) about students (called TblStudent, another about Exam Boards (TblExamBoard) and another about courses (TblCourses). Tables are linked together using Primary and Foreign Keys. The Primary Key (the unique identifier field) from one table becomes as foreign in the table it is linked with. See top of p. 98 for an example.
Entity Relationship Diagram (ERD) The relationship between tables or entities in a rdb is shown using an ERD. These relationships are usually one to many or many to one For example, each student will study many courses (one to many) Many courses will offered by one exam board (many to one) (There are one to one relationships but not in this example. ERDs use a crows feet diagram to indicate the type of relationship i.e See pg 97 for some further illustrations CAT FLEAS
Put into practice Now let’s put that theory into practice. Include another table to separate information about subjects from the student table. Create an attributes table and ERD of the 2 tables. Now lets build... CONSOLIDATE: In your notes answer questions 1 – 3 on p 110 -113
Attributes Tables These are used in design process to tell the develop which fields are needed in each table, indicating the Primary Key (PK) and Foreign Key (FK) fields in each table. TASK: Include another table to separate information about subjects from the student table. Create an attributes table and ERD of the 2 tables. Tbl Subjects Tbl Students Student ID Surname First name Reg Group Predicted Grades PK PK Subject ID Subject Title Exam Board Exam (%) Student ID FK
Key Words Key Words Data Redundancy Entity Validation ERD Primary Key Foreign Key