Database Design

Card Set Information

Database Design
2013-05-13 11:15:47

Database Design
Show Answers:

  1. Name two of the most popular types of index.
    Hash Table and a b-tree index.
  2. What is a b-tree index?
    The B-tree index is a command used method of storing index data. The indices are organised as a balanced tree, similar to a binary tree structure.
  3. What are the disadvantages of using an index?
    Takes up space on the disk.

    Slows down performance.

    Both the index and the table data must be updated.
  4. What does it mean when an index is sparse?
    An index is sparse if there are very few records matching a specific key value.
  5. What does it mean when an index is dense?
    A dense index has lots of records matching a key value.
  6. How does an index work?
    An index is a separate data structure that uses the indexed field value as a lookup key. The index then returns the physical location where records holding that value are stored.
  7. What is a hash table?
    The hash table applies an algorithm to the key value to give the physical block no. i.e. Blockid = f(key).

    This is fast as there is no lookup table involved.
  8. What is indexing?
    Indexing on a table is a mechanism that provides direct access to the physical disk block location of table records, based on the values contained in certain indexed columns.

    The SQL Server engine uses an index in much the same way a reader uses a book index.
  9. Describe the CRUD operations.
    • Create a record - INSERT INTO
    • Read a record - SELECT
    • Update a record - UPDATE
    • Delete a record - DELETE
  10. What are the advantages of using an index?
    The use of an index can greatly improve the performance of SQL SELECT statements.
  11. What is the role of a Primary Key in a database?
    • The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique or can be created like UserID or something like that. Each time you have data inside a relational table, you need a way to identify each row stored into that table, for this you need an element or
    • group of elements that is unique for each row. This element or elements is the primary key.
  12. What is the role of a Foreign Key?
    To define a relationship or link between two tables, you use a Foreign key for the dependent table. This foreign key is the primary key of the "parent" table. The rule to use is: Every non-null foreign key value must match an existing primary key value.
  13. What are the main purposes of a database transaction?
    Defined by the acronym ACID, which stands for Atomic, Consistent, Isolated and Durable. This means that when doing an INSERT, UPDATE or DELETE they are usually permanent. When one of these actions is done it is independent of any other operation. It doesn't interfere with any other operations and it's persistent, it's there for good.
  14. What is a database Transaction?
    A database transaction, by definition, must be atomic, consistent, isolated and durable. Database practitioners often refer to these properties of database transactions using the acronym ACID.

    • Example:
    • Examples from double-entry accounting systems often illustrate the concept of transactions. In double-entry accounting every debit requires the recording of an associated credit. If one writes a check for €100 to buy groceries, a transactional double-entry accounting system must record the following two entries to cover the single transaction:
    • 1. Debit €100 to Groceries Expense Account
    • 2. Credit €100 to Checking Account
  15. What is a View?
    A view is a virtual table.

    These tables are dynamic which means they change when the data changes.

    A view is useful because when you save a new view it saves you having to type out that query again.
  16. Give an example of a View in SQL Language.
    • CREATE VIEW view_name AS
    • SELECT column_name(s)
    • FROM table_name
    • WHERE condition
  17. Give an example of a select query.
    • SELECT column_name,column_name
    • FROM table_name


    SELECT * FROM table_name
  18. Give and example of an INSERT INTO query.
    The first form does not specify the column names where the data will be inserted, only their values:

    • INSERT INTO table_name
    • VALUES (value1,value2,value3,...)

    The second form specifies both the column names and the values to be inserted:

    • INSERT INTO table_name (column1,column2,column3,...)
    • VALUES (value1,value2,value3,...)
  19. Give an example of a Transaction.
    • INSERT INTO accounts ( acc_num, acc_type, acc_bal)
    • VALUES (100,'checkings_account',5000)

    • INSERT INTO accounts ( acc_num, acc_type, acc_bal)
    • VALUES (100,'savings_account',5000)

    • COMMIT // this is to confirm the transaction
    • ROLLBACK// if there is a mistake this undo's it
  20. What are the disadvantages of a hash table?
    The disadvantage is that the algorithm may return the same Block no. for many different key values and there “cluster” the records in a small number of blocks.
  21. What's an advantage of a B-tree index?
    B-tree Indexes are relatively easy to update when a new record is inserted or a record is deleted/updated.
  22. How do you add a foreign key called "PersonId" to a table called "Orders" in sql query language?
    • ALTER TABLE Orders
    • ADD FOREIGN KEY (PersonId)
    • REFERENCES Persons(PersonId);
  23. How to create a table called "Customers" with fields called CUST NO, NAME, ADDRESS and ORDER NO (FK)?
    • CREATE TABLE customer
    • (
    • Cust no. int PRIMARY KEY,
    • Name varchar(50) NOT NULL,
    • Address varchar(50) NOT NULL,
    • Order no int NOT NULL,
    • FOREIGN KEY (Order no) REFERENCES Order(Order no)
    • );
  24. List the names all employees in the “Finance” department.
    • SELECT Name
    • FROM employee
    • WHERE Dept = 'Finance';
  25. List the name and cost centre of all employees in “Finance” or “R&D”.
    • SELECT CostCentre, name
    • FROM employee, Dept
    • WHERE Dept = 'Finance' or 'R & D';
  26. List the number of employees in the “Production” department.
    • SELECT count(dept)
    • FROM employee
    • WHERE Dept = 'Production';
  27. List the name, department and salary of all employees in ascending order of department and salary.
    • SELECT Name, Dept, Salary
    • FROM employee
    • ORDER BY Dept, Salary ASC;
  28. Create an index on the EMPLOYEE table StartDate column.
    • CREATE INDEX date_started
    • ON employee (StartDate);
  29. List the oldest employee in the company.
    • SELECT MIN(StartDate)
    • AS oldest_employee
    • FROM employee;
  30. Change the salary of employee 100 to 50,000.
    • UPDATE employee
    • SET salary = 50000.00
    • WHERE EmpNo = 100;
  31. List the number of “Finance” employees who are older than employee number 100.
    • FROM employee
    • WHERE DOB > (SELECT DOB FROM employee WHERE EmpNo = 100)
    • AND dept = 'Finance';