[Database class] Midterm answers

[Paul S. Wang]

Dear dbclass,

I am attaching the "best answers" to questions.

Paul
-- 
==============================================================================
 Paul S. Wang (王士弘)                      Email: pwang at cs.kent.edu            
 Dept. of Computer Science, Kent State U.   Tel: (330) 672-9051
 Kent, Ohio, USA 44242-0001                 http://www.cs.kent.edu/~pwang/ 
==============================================================================
-------------- next part --------------
1-a

database:  A collection of data items organized for easy storage, 
           fast retrieval, and shared concurrent access by multiple users

relation:  A relation is a set of one or more related attributes for 
           some particular entity and the domain of possible values for 
	   each attribute.  In other words, a relation is a table schema.

attribute: An attribute is the name of a property, characteristic, or value
           belonging to some entity, such as the title, age, or salary 
	   attribute of an employee.

domain:    The domain of an attribute is the set of all possible values for
           the attribute.

tuple:     A tuple is a list of attribute values (i.e. a table row)
           in a relation instance (i.e. a table instance).
           A particular relation instance (a table instance) is a collection
	   of distinct tuples.


1-b

database server address (hostname or IP)
userid
password
database name

2
create table author   -- assumed and not required on exam
(   id  varchar(10),
    name varchar(40),
    primary key (id)
) ENGINE = InnoDB;

create table book
        (ISBN           varchar(13) NOT NULL,
         author_id      varchar(10), 
         title          varchar(75),
         price          numeric(6,2) check (price > 0),
         primary key (ISBN),
         foreign key (author_id) references author (id)
                on delete set null
        )  ENGINE = InnoDB;


3-a
http://dbdev.cs.kent.edu/database/notes/key.html

3-b

Data aggregation in SQL means combining data values from all or
a set of table rows for an attribute.

Give example use such as 

select SUM(amt) as total_sales from sales_table;

select AVG(amt) as average_sales from sales_table;

4.

See lecture notes