Question 1: Multiple Choices

Question 1: Multiple Choices
FILE PROCESSING SYSTEM AND DATABASE BASICS

1. Which of the following is NOT seen in a file processing system?
A. Table
B. Metadata
C. Data
D. Attribute

2. If I split up the “address” attribute in my file processing system into two new attributes: “city” and “state”, the Java and the JSP program on the web server that access the file system has to change accordingly. This is problem is called:

A. Update Anomaly
B. Insertion Anomaly
C. Data Dependency
D. Redundancy

3. Which of the following is NOT a key component of a database?
A. Metadata
B. Relationship
C. Attribute
D. Table

4. __________________ is a software package that facilitates the creation and maintenance of a database.

A. Database
B. Database System
C. VISIO
D. DBMS
CONCEPTUAL DESIGN

5. _________ in conceptual design is the counterpart of primary key in logical design.

A. Attribute
B. Derived attribute
C. Identifier
D. Composite attribute
6. Entities of a given type are grouped into:
A. Database
B. Entity class
C. Attribute
D. ERD

7. Read the following ER. Which interpretation is NOT CORRECT?

A. An employee could work at no department at all
B. A department could have many employees
C. A department does not have to have any employee
D. An employee could work in at most one department

8. Which of the following Crow’s foot notation indicates a mandatory participation?

9. A building has to have at least one room, but usually has many rooms. On the other hand, a room needs to be in a building to exist, and can only be in one specific building. The best depiction of this relationship is:

10. Entities can be associated with one another in which of the following?

A. Entities
B. Attributes
C. Identifiers
D. Relationships

LOGICAL DESIGN

11. A relation is considered a:

A. column
B. two-dimension table
C. one-dimension table
D. three-dimension table

12. Which of the following is nota restriction for a table to be a relation?

A. The cells of the table must contain a single value.
B. All of the entries in any column must be of the same kind.
C. The columns must be ordered.
D. No two rows in a table may be identical.

13. Row is synonymous with the term:

A. Attribute
B. Record
C. Column
D. Field

14. Which of the following relationships in ER translates into a table in logical design?

A. 1 – 1
B. 1 – m
C. M – m
D. None of the above

15. Which of the following is a group of one or more attributes that uniquely identifies a row?

A. key
B. determinant
C. tuple
D. relation

NORMALIZATION

16. When the values in one or more attributes being used as a foreign key must exist in another set of one or more attributes in another table, we have created a(n):

A. Transitive dependency
B. Insertion Anomaly
C. Referential Integrity
D. Normal Form
17. A field called “birth date” cannot have value of “Oct. 32nd”. This is an example of:

A. Entity Constraint
B. Referential Integrity
C. Domain Constraint
D. Business Constraint

18. If attribute A determines both attributes B and C ( A ? BC), then it is also true that:

A. A ? C
B. B ? C
C. B ? A
D. C ? A

19. Every time attribute A appears, it is matched with the same value of attribute B, but not the same value of attribute C. Therefore, it is true that:

A. A ? BC
B. A ? B
C. A ? C
D. B ? A

20. For a relation R = (A,B,C,D), we know that A ? B and B ? D. What is the candidate key of R?

A. A
B. AB
C. AC
D. AD

Question 2: True or False (10 questions, 10 points)

1. A relation has cells that hold multi-value entries

2. In a relation, the order of the rows matters.

3. If a table is in 2nd normal form, there is no transitive dependency

4. There could be multiple candidate keys in a table

5. A many-to-many relationship is always translated into a table

6. An ideal primary key is short, numeric and seldom changing

7. The relationship in ER is implemented through the foreign key in logical design

8. Weak entity is ID-Dependent entity

9. A derived attribute will not be translated into an attribute in a table

10. A relation, by definition, is in 1st normal form

Question 3: Short Answers (20 points)

1. List THREEstages of the database development lifecycle. (3pts)

2. What is super/subtype? Why do we use supertype/subtypes? Give an example of entity supertype/subtype. How do we translate entity supertype/subtype from an ERD into a relational schema (You can use an example to demonstrate)? (3pts)

3. Is SQL relational? Why? (3 pts.)

4. In this SQL query (3 pts.)

SELECT fname, cname, rent_day
FROM film f, customer c, rent r
WHERE f.fid = r.fid AND c.cid = r.cid;

What’s the purpose of the WHRER clause?
How would the result be different if the WHERE clause were missing?

5. There is a table Employee (Emp#, SSN, name, City, State, Zip, Salary)
Some users are allowed to see some information, but not SSN and Salary, of employees. What mechanisms would you use to enforce this access policy? Please describe step by step how you would achieve this goal (you don’t have to give SQL commands)(3 pts.)

6. You just created the “student” table for your database. You wrote a script that inserts 23,007 rows into this table. You are now ready to run it. How many rows will be in the table if:(5 pts.)

(a) You executed the script in Oracle SQL Developer. Right after you started the transaction, SQL Developer was aborted. You only noticed the message “1 row inserted” twice.

(b) You executed the script in Oracle SQL Developer. After 20,000 records had been inserted, Oracle SQL Developer was aborted.

Case

Continental Palms University (CPU) plans to develop a database system to keep track of its various aspects. As a first step, your team has been hired as a consultant to quickly build a prototype system. As is usual in prototyping, the case is greatly, while still reasonably, simplified to allow a fast development. However, the prototype must capture at least the requirements specified in the subsequent paragraphs. You are of course free to explore more aspects of the university and exploit more details. But make sure you will deliver the required prototype system on schedule. You are warned that it is extremely likely there are incompleteness, ambiguities, and inconsistencies in the description and you are responsible for resolving the potential problems.

First of all, the purpose of the university is primarily to provide education to students. The foremost important information that must be captured by the database is, therefore, that of students. We need to record their student ID, name (first name, middle initial, and last name), gender, address (street, city, state, zip), phone number, email address, GPA, country of birth, religion, etc. For most students, her student ID is her social security number. But some international students don’t have a social security number and are allocated a student ID when they enroll in the university. The use of social security number as student ID has raised some security and privacy issues. The university is planning to stop this practice and assign every student a unique ID that is different from her SSN. The use of SSN will then be controlled. For undergraduate students, we also want their class standings (i.e., freshman, sophomore, junior, and senior). For graduate students, we want their thesis titles and academic advisors. The academic advisor of a graduate student should be a faculty member in the student’s major department. Every student must have a major in some department and may optionally have a minor in a different department. However, she doesn’t have to decide on her major and minor before she enters advanced standing.

CPU has several colleges, such as college of medicine and college of business and public administration. We need to store the name, location, phone, fax, dean, and so on of each college in the database. There are a number of departments in each college. We need similar information about each department too. A college dean or a department head must be a faculty member in the college or department and must be a tenured associate or full professor. CPU is growing quickly in recent years. It is predicted that more departments and even colleges will be installed in the near future.

Every employee of the university is assigned to a position in some department. Upon employment, every employee is allocated an employee number. This number is unique in the entire university and is frequently used to identify the employee in various on-campus operations. The social security number of each employee is also recorded, but is only accessible to some strictly controlled groups, such as payroll. Other information about each employee includes name, gender, address, phone number, email address, position, salary, etc. Each employee is classified as a staff or a faculty member. A staff should possess a major specialty. A faculty member should have a title (e.g., lecturer, assistant professor, associate professor, professor, etc.), a set of teaching interests, and a set of research interests. We also need to know the highest degree of each faculty member and where and when that degree was obtained.

Each department offers a set of courses. Each course has a course number, title, number of units (between 1 and 12), and description. A course may require some other courses as prerequisites. Several sections may be offered for each course in each semester, which is characterized by season, year, and duration (between a starting date and an ending date). Different sections of the same course may differ in lecture time, classroom, number of total seats, number of open seats, etc. Each section is taught by a faculty member and may have several TAs, who are graduate students. Both the instructor and the TAs may hold office hours. Often the instructor is the academic advisor of the TAs but of course there are exceptions.

When a student registers for a course, she must select a particular section of it. A student may take the same course twice in different semesters, but can’t take two sections of the same course in a single semester. The grade she gets for that class at the end of the semester may be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, or ‘I’. A student who gets an incomplete grade (‘I’) is required to complete the course in one year. If she can’t she will get an ‘E’. Each international student is required to take at least six units a semester in order to keep her student visa status. Each graduate TA must take six to twelve units, among which at least six must be for graduate level courses, and must keep a GPA of at least 3.5, in order to be eligible for the financial assistance.
Case Question 1: Based on the above, develop the ERD for CPU. (15 points)

Directions: You do NOT have to use VISIO for your model. In case you prefer hand-drawing, please make sure that you scan or photo your solution CLEARLY into digital copy. You could use either the original or the Crow’s Foot notational system for your modeling. No matter what notational system you are using, make sure that you have a text box for specifications / assumptions.

Case Question 2: Based on your ER, create the relational model for CPU(15 points)

Directions: Please refer to the relational model assignment for format of this. You need to specify tables, attributes, primary keys and foreign keys. I will base my grading on whether you faithfully translated your own ER into relational model. In other words, if you made mistakes in your ER model, those mistakes would not be taken into consideration in grading this question.

Case Question 3: Based on your relational model, develop SQL codes for the following queries: (20 points)

a. List title of all three-credit courses offered by the IS department.

b. List all international students who have trouble keeping their visa status for fall 2013 (a very useful query for registrar!)

c. List the average number of students for course IS 331 in the past five years by semester

d. List the number of employees by department in descending order (with the largest department in the first row)

e. List the departments that do NOT offer any 1-credit courses

f. List candidates for honor students for spring 2013. Assume that an honor student candidate is a student who gets all A’s in the classes he / she takes.

g. Since some graduate students may not have a thesis (e.g., first-year Ph.D. and some master students), we want to list the percentage of graduate students WITH a thesis by department.

h. The president is concerned by complaints from some faculty members that they are underpaid. As a result, you need to develop a query to show him the average salary by department. In order not to “distract” him, list ONLY THE DEPARTMENTS WITH AVERAGE SALARY lower than 80,000

i. List all Graduate TA’s who are eligible for financial aids

j. List PAIRS of faculty members that share the same research interests (to simplify the query, assume that each faculty member has just ONE research interest – but when you have time, try tackle the more realistic scenario where each person could have multiple interests!)

ORDER THIS ESSAY HERE NOW AND GET A DISCOUNT !!!