2001 Fall CS157A Midterm 1 Sept, 27,2001

Prof. Sin-Min Lee Name:_____________

1.(5x2=10 marks)Define each of the following database terms accurately and concisely.

A)Data model---- A set of concepts to describe DB structure (it’s data types, relationships, constraints) and a set of basic operation to describe retrievals and updates. Sometimes, a set of valid user-defined operations. (classes of data models are conceptual, representational and physical) -

B)tuple----- A row in a relation table.

C)relation database----- A relational database stores all its data inside tables, and nothing more. All operations on data are done on the tables themselves or produces another tables as the result.


D) DML-----is acronym of data-manipulation language. It is a language that enable users to access or manipulate data as organized by the appropriate data model. They are basically two types: Procedural DML or Declarative DML.

 

E) schema-----A schema is a description of a particular data, using the particular data model. For example in relational data model, every relation has a schema, which describes the columns or fields.

 

2. (2+2+6=10 marks)

c. Find all the other keys beside {ID}.

Solution: { Name}, { Date Registered}, {ID, Name}, {ID, State}, {ID, DateRegistered}, {Name, State}, {Name, DateRegistered}, {State, DateRegistered}, {ID, State, DateRegistered}, {ID, Name, DateRegistered}, {ID, Name, State},{Name, State, DateRegistered}, {ID, Name, State, DateRegistered}

3. (5 marks) Fill in the blank: The three levels of data abstraction are : view, logical , and physical (or external, conceptual, internal)

4.(5 marks) Explain and give an example of the following terms from the relational model:   (i)   primary key   (ii)  foreign key
  Solution: Primary Key: Attribute(s) uniquely identifying each relational tuple
       Must be minimal
       Give Example                                                                                          Foreign Key: Primary Key from another table providing link (relationship) with
       that table     Must contain either valid primary key values or null
 

  5.(3+3+4=10 marks) (a) What is the maximum number of rows in a relation created by the Cartesian product of 2 relations? (let M = number of rows in table 1 and N = number of rows in table 2)

Answer: M *N (product of the # rows in the two tables)

(b) If the relation A has 40 tuples and the relation B has 20 tuples, what is the size of the cartesian product?

Answer: 40x20=800

( c) If you perform a project operation on a table with N rows, you will sometimes get less than N rows returned. Under what condition will this occur?

Answer: When duplicate rows exists – (a relation is defined as a set)

6. (5 marks) Use examples to describe the difference between strong entity type and weak entity type.

Solution: A strong entity type has its own primary key, while a weak one does not.

For example, EMPLOYEE is a strong entity type. Its primary key is SSN. DEPENDENT of employee is a weak entity type. It doesn’t have its own primary key. To uniquely identify a dependent, we need to know the SSN of the corresponding employee and the dependent name. The dependent name may not be unique.




7. (5 marks) Find CUSTOMER ¸ STOCK

 

 

8. Multiple choice (10x3 =30 marks)

(1) What objects are found in a RDBMS? (a)tables (b) forms (c) queries (d) All the above objects are found in a RDBMS

(2) Which of the following is not a guideline to use when designing a database?

(a) Determine what information should be stored.

(b) Define the fields and determine the primary key for each table

©Divide information into named tables.

(d)Determine when the database needs to be copied.

(3) The data stored in an individual field in a single record is called:

  1. a record.(b) an entry.(c) a field name.(d) a file.

(4) A record is a(n):

  1. entry box (b) collection of all the information in a database
  2. unique row of data stored in a table (d) number in a field.

(5) A relational database:

  1. is any data that is stored on a computer
  2. organizes and stores data in tables.
  3. groups unrelated pieces of information together in a form.
  4. refers one database with another database one entry at a time.

(6) In database development the design phase is divided into two parts, i.e.

    1. requirements analysis design, table design
    2. implementation design, maintenance design
    3. conceptual design, maintenance design
    4. conceptual design, table design
    5. maintenance design, requirements analysis design

(7) In a relational system, data in one Table can be "related" to data in another, allowing you to tie together data from several Tables

    1. true
    2. false
    3. true only for MS-Access
    4. true except for MS-Access
    5. none of the above

 

8. Another name for tuple is:

    1. row (b) table (c)column (d) query (e) relation

9. A disadvantage of going in for a DBMS is

    1. initial costs in terms of hardware, software and personnel
    2. a computer installed with a DBMS can’t access the Internet
    3. it is not considered fashionable in computer industry circles
    4. since Word and Excel allow you to enter any kind of data in their table columns, they are preferred
    5. databases are outdated

10. Which of the following statements is false concerning the selection of primary keys? (Circle the letter indicating the best answer)

(a)The key should not change its value over the life of each instance of the entity type.

(b)The key must be guaranteed to have valid values, and not be null

©Coded keys (whe re the structure of the key has special meaning) are preferred.

(d) Single attribute keys are preferred over composite (multiple attribute) keys

The correct answer is c

9. (5 marks) For the following ER-diagram

Translate it in a relational model. Show all primary and foreign keys.

Solution:

10.(15 marks) The SJSU to create a database system to keep track of the information about students and courses.

Each student is assigned a unique student ID when she enrolls in the university. For example, Beth Roberts is assigned a SID ‘887451234’. The database should also record each student’s name (first name and last name), gender, major department, and GPA.

SJSU offers various courses to students. Each course is uniquely identified by a course number. Other information about a course includes course title, number of units, and department. The number of units of a course is between 1 and 9.

The database should record the semester and grade whenever a student takes a course. The GPA of a student is calculated based on her course grades. It’s SJSU’s policy that any student must take more credits in her major department than in other departments.

Perform conceptual design for the given case. You must generate both an ER diagram and a specification that includes additional constraints not shown on the diagram. The specification should be written in English. If you believe that you need to make additional assumptions, clearly state them.

Solution:. Conceptual design----ER diagram

 

Additional Constraints: