Database Domain 1 Lesson 1 to 3 Primary Keys When designing a table, one should designate a field (or multiple fields) as a primary key. This field tells the viewer what the key component of the table is. If more than one field is necessary, a compound key can select two fields as the key. One can select and hold (or rightclick) the table and then select Design to see active keys. Primary and compound keys are essential to database design as they ensure that every record in a table is unique. Step for Completion: 1. What field ensures that every record in a table is unique? Answer: Primary Keys 2. What key is used when two fields need to be the primary key? Answer: Compound Keys Data Types When looking at a table’s design features, one can see the types of data that are used. Most data types use text or numbers in some form. Different types of data use varying amounts of storage. The amount of memory available to store data helps a designer choose which data types to use. A designer should consider a data type that stores the necessary data while using as few bytes of storage as possible. Step for Completion: 1. In which relationship are primary keys used from two different tables? Answer: A Foreign Key relationship 2. What is the purpose of an entity-relationship diagram (ERD)? Answer: To visually represent the structure of a database. 3. What is the default setting of the Enforce Foreign Key Constraint option when a relationship is created? Answer: Foreign key values in the referencing table must correspond to the primary key values in the referenced table 4. Enforcing referential integrity prevents what from happening in a foreign key table? Answer: Orphaned Records and Cascade Deletion Establishing Relationship There are primary keys for each entity in a database. Foreign keys can link the data between tables together. Foreign keys use either a one-to-one or one-to many relationships. One way to organize relationships is to create an entity relationship diagram (ERD). An ERD is an effective way to plan a database’s structure using columns and rows. These plans adjustable. Referential integrity ensures that a primary key value exists before a foreign key value. This rule enforcement is done by enforcing foreign key restraints. Normalization Normalization is the process of ensuring that an entity in a database has a solid design and that data in an entity can work well with other entities. In first normal form (1NF), each field needs to have a single value. If there is more than one value in a field, data will not sort properly. In third normal form (3NF), a value change in one table should not necessitate a value change in another field from the same table. Data Protection Measures It is important to back up a database regularly in case information is deleted or changed. Ideally, a backup should go to a different drive than what is hosting that database. Backing up and securing a database should be done regularly. To secure a database, one should use the principle of least privilege. This principle states that people should have the permissions they need to do their job and nothing more. A database should follow this principle first on a database level and then on an object level. With objects, specific privileges can be granted to and revoked from users and roles within database objects.