Databases

Databases

1. Test

   • Databases
     • What is meant by the term 'database'
       • A structured set of storage to hold data
     • Difference between flat-file and relational
       • Flat FIle
         • Contains a single set of data, a table
       • Relational
         • Contains multiple tables connecting via foreign keys and primary keys
         • Advantages
           • More organised, neater
           • Less physical storage needed
     • DBMS
       • Describe what is meant by a DBMS
         • A database management system provides an interface for the user
       • A DBMS allows the separation of applications from the data. Why is this important?
         • If you want to change the structure it won't affect the data
     • Tables
       • Explain the relationship between entities and tables
         • An entity is a single person, place or thing where data can be stored
       • Explain the difference between a primary key and a foreign key
         • A primary key keeps the value unique while a foreign key connects 2 tables
     • Advantages of electronic database
       • You can search for a specific item
       • Takes up less physical room
   • SQL
     • Describe what is meant by SQL in regards to databases
       • Structured Query Language is a language used to query your databases. You can add items, delete items and also search for items.
     • Queries
       • Table name: Members | 14343 | Fiona | Gibson | GF6 3KL | 07722 235678
         • Write an SQL statement to add this record to the database
           • INSERT INTO 'Members' WHERE('ID','FirstName','LastName','PostCode','PhoneNo') VALUES (14343, 'Fiona', 'Gibson','GF63KL',07722235678);
         • There has been a mistake in one of the data items in the table. Change the postcode to 'HF5 3SF'
           • UPDATE 'Members' SET PostCode = 'HF5 3SF' WHERE PostCode = 'GF63KL';
         • Extract all information in the FirstName and LastName fields
           • SELECT * FROM 'Members' (FirstName, LastName);
   • Validation, Verification and Data Integrity
     • State what is meant by the following terms.
       • Data Integrity
         • The answer that is expected
       • Validation
         • Check if answer is reasonable
       • Verification
         • Check if data is true
     • Describe the following methods of data validation
       • Presence check
         • Check if all information has been included
       • Range check
         • Check if answer exceeds maximum
       • Format check
         • Check if answer matches the data type

2. Queries

   • SELECT * FROM table_name;
     • Selects all data from 'table_name'
   • INSERT INTO table_name VALUES (val1, val2);
     • Inserts values into 'table_name'
   • UPDATE 'table_name' SET column1 = "value" WHERE column1 != "value";
     • Update 'table_name' where column1 doesn't equal 'value' and set it to 'value'
   • DELETE FROM table_name WHERE column1 = "Delete me!";
     • Delete where column1 is equal to "Delete me!"