ITM 354 1-8
What are the purposes of implementing views?(QUIZ 7)
) Facilitate retrieval operations 2) Simplify query specifications 3) Security measure
19. A PARTS file with Part# as hash key includes records with the following Part# values: 7, 12, 21, 60, 89, 90, 41, 15, 76, 19, 25, 36. The file uses 8 buckets, numbered 0 to 7. Each bucket is one disk block and holds two records. A. Show how you would load these records into the file in the given order using the hash function h(K)=K mod 8. B. Calculate the average number of block accesses for a random retrieval on Part# for the file: (Quiz6)
...
7.b Use subqueries to write the following query and determine whether it's correlated or non-correlated. Indicate its internal memory required and the processing time (evaluations needed). 02. Find the full names of all employees who either is of the same gender of his supervisor or have the same birthday as his/her manager. (5 points)(Quiz4)
02.A. SELECT Fname, Minit, Lname FROM Employee E WHERE EXISTS (SELECT * FROM Employee S WHERE E.Superssn=S.Ssn AND E.Sex=S.Sex) OR EXISTS (SELECT * FROM Employee M, Department D WHERE M.Ssn=D.Mgrssn AND E.Dno=M.Dno AND E.Bdate=M.Bdate AND E.Ssn<>M.Ssn) B. Memory: 200 + 200 + (200 * 15) C. Operation: (200 * (1 + 200 * 2)) + (200 * (1 + (200 * 15) * 4))
11. What are the three levels of organizational tasks and decision-making? Associate the following terms with each level: DB, Data warehouse, OLTP, OLAP, EIS, DSS, web crawler, semi-structured data, structured data, unstructured data, data marts, informating, data mining (4 points)(Quiz2)
1) EIS (Executive Information System, Executive Level) a) OLAP b) Data Mining c) Web Crawler d) Unstructured Data 2) DSS (Decision Support System, Managerial Level) a) OLAP b) Semi-Structured Data c) Data Warehouse d) Data Marts 3) DB (Operational Level) a) OLTP b) Structured Data c) Informatting
RESULT 12 13
1. Show the resulting relation of the division operation: Relation AA / Relation BB. (1 points)(Quiz5)
Which of the following concepts is/are relevant to query optimization?
1. System Catalogue 2. Communication cost 3. Selectivity of records 4. Record size 5. Views 6. Block size 7. Recursive decomposition of queries (QUIZ 8)
5. Determine the highest normal form (your learned so far) of the following STUDENT relation and detail how you arrived at your answer, meaning explain the rules. If STUDENT relation is not in 3rd Normal form, propose a solution to derive a 3NF design. (3 points) STUDENT (Student_ID, Address, Birthday, Phone) FDs: Student_ID → Birthday Student_ID → Address Address → Phone (Quiz6)
1NF: no composite attribute, no multivalue attribute, no repeating group.
AA.C_ID AA.Name AA.Ad BB.O_ID BB.P_ID BB.C_ID 2 x a _______________100 P2 2 4 y b _______________NULL NULL NULL 9 z c _______________NULL NULL NULL NULL NULL NULL_____300 P2 3
2. Give the result of the following SQL Query. (3 points)(Quiz5) SELECT * FROM AA full outerjoin BB on (AA.C_ID = BB.C_ID);
WRITE IT OUT SHOULD HAVE 7 Referential Integrity (Quiz6)
3. Transform the following ERD to a relational logical design in at least 3rd Normal form. (7 points)
5. Retrieve the department name and number of male employee for each department that has 5 female employees making more than average salary of all employees. (5 points) 01. Analyze the performance of this query. (2 point) 02. Is the query correlated or non-correlated? (1 point)(Quiz4)
A. SELECT Dname, COUNT(*) AS [Number of Male Employees] FROM Department, Employee WHERE Dnumber=Dno AND Dno=(SELECT Dno FROM Employee WHERE Sex="F" AND Salary > (SELECT AVG(Salary) FROM Employee) GROUP BY Dno HAVING COUNT(*) >5) AND Sex="M" GROUP BY Dname; 01.A. Memory: (15 * 200) + (200) + (200) 01.B. Operation: (15 * 200 + 4) + (200 * 4) + (200 * 1) 02.A. Non-Correlated
6. Calculate the number of male and female employees and total salary of male and female employees in each department. (2 points)(Quiz4)
A. SELECT Dno, Sex, COUNT(*) AS Num_Employee, SUM(Salary) AS Total_Salary FROM Employee GROUP BY DNO, SEX
7.a Use subqueries to write the following query and determine whether it's correlated or non-correlated. Indicate its internal memory required and the processing time (evaluations needed). 01. What are the first names of female employees who have more than two dependents, earn more than average salary in the company and are supervised by Mr. Wong? (5 points)(Quiz4)
A. SELECT Fname FROM Employee WHERE Sex="F" AND Ssn IN (SELECT Essn FROM Dependent GROUP BY Essn HAVING COUNT(*)>2) AND Salary>(SELECT AVG(Salary) FROM Employee) AND Superssn IN (SELECT Ssn FROM Employee WHERE Lname="Wong") B. Memory: 200 + 200 + 250 + 200 C. Operation: (1 x 200) +(200) +250 + (4 x 200)
1. Use NOT EXSITS function: List the first names of female employees in Department #5 who have no dependent. (2 points) 01. This type of Query is called 02. What is the internal memory requirement? 03. What is the number of operations/comparison that need to be done? 04. What is the advantage of this type of query compared to single-block query that utilize Cartesian product operation? (4 points)(Quiz4)
A. SELECT Fname FROM Employee A WHERE A.Dno="5" AND A.Sex="F" AND NOT EXISTS (SELECT * FROM Dependent D WHERE A.Ssn=D.Essn); 01.A. Correlated Query 02.A. 200+250 03.A. 200 * (1 + 250 * 3) 04.A. Memory space requirements are significantly less for the correlated query.
2. Use outerjoin operation (in ACCESS syntax) for the same question above. (2 points)(Quiz4)
A. SELECT Fname FROM Employee E LEFT JOIN DEPENDENT D ON E.Ssn=D.Essn WHERE E.Dno="5" and E.Sex="F";
4. Use a subquery: List the last names of supervisors who supervise more than 2 employees or last names of employees who have 2 or more dependents. (4 points) 01. Provide the performance (2 points) 02. Is the query correlated or non-correlated? (1 point) (Quiz4)
A. SELECT Lname FROM Employee WHERE Ssn IN (SELECT Superssn FROM Employee GROUP BY Superssn HAVING COUNT(*)>2) UNION SELECT Lname FROM Employee WHERE Ssn IN (SELECT Essn FROM Dependent GROUP BY Essn HAVING COUNT(*)>=2) 01.A. Memory: (200 + 200 +200 + 250) 01.B. Operations: (200 + (1x200)) + (250 + (1x200)) A. Non-Correlated
16. If an unordered file has 2,000 records and each disk block contains 5 records, on average how many disk accesses will be required to retrieve a random record from the file: ___________________ (1 pt) (Quiz6)
A. (2000/5)/2=200 accesses
17. What is the loading factor for a B+ tree? (Quiz6)
A. 50%
18. What is the loading factor a B* tree? (Quiz6)
A. 67%
15. To reduce overflow records a hash file is typically kept ______% full (1pt) (Quiz6)
A. 70-80% Full
21. What is the advantage of B-tree over Balanced Tree?(Quiz6)
A. A B-tree can handle insertions and can have a variable number of children. It is also short and fat to reduce access time.
1. What is a relationship relation? (1 point) (Quiz6)
A. A relationship relation is a relation created from a Many-Many relationship.
9. b) ISAM is a. balanced tree; b. hardware independent; c. by IBM; d. good at handle insertion; e. none of the above. (Quiz6)
A. A. Balanced Tree; C. By IBM
20. What does B-tree stand for?(Quiz6)
A. Bayer, Boeing, it is unknown.
10. Which is/are the correct statement(s)? (2 points) (Quiz6)
A. C) A block is the unit of transmission between main memory and secondary storage
11. How does a clustering index differ from a primary index?: (Quiz6)
A. Clustering index differs in that the index is based on a non-key field, such as a grouping.
2. Define a database: including general description and 4 characteristics (5 points)(Quiz1)
A. Collection of related data organized by a certain data model to facilitate data retrieval for decision-making purposes. 1. Miniworld as a data source, the Universe of Discourse 2. Logically Integrated Files 3. Intended Users and Applications 4. Shared and Self-Describing
3. Detail the differences between database (which is defined in question 2), DBMS and DBS. Spell out DBMS and name a commercial DBMS_________________. (5 points)(Quiz1)
A. Database is the collection of related data organized by a data model. B. DBMS is a collection of software to facilitate the creation and maintenance of a database. C. The DBS is the combination of the DBMS and database together sometimes with applications.
7. What is embedded SQL? (1 points) What is canned Query? (1 point) (Quiz5)
A. Embedded SQL is an SQL statement written within the code of another programming language. B. Canned Queries are pre-defined queries, with set parameters that are not expected to change, and can be reused.
14. An unordered file is also called a ____________________ (1 pt) (Quiz6)
A. Heap or Pile
6. Write a SQL statement to insert a record: John T. Dote with SSN (999-97-7777) to the employee table. John is assigned to department 6. Explain what errors (violations) will happen when you execute this command. (2 points)(Quiz5)
A. INSERT INTO Employee(Fname, Minit, Lname, Ssn, Sex, Dno) VALUES ('John', 'T', 'Dote', '999977777','M',6); B. A Referential Integrity violation will occur. There is no Dnumber '6' that exists in the Department Table for which the Dno field of the Employee table refers.
12. What does ISAM stand for: (Quiz6)
A. Indexed Storage Access Method
5. Name 4 major advantages (or reasons) for adopting a database approach for information management in an organization. (1 point each)(Quiz1)
A. More Information from Given Data. B. Ad Hoc Queries can Be Performed. C. Redundancy can be Reduced. D. Inconsistencies can be Avoided. E. Security Restriction can be Applied. F. Data Independence.
5.A. Compute the average of salary for male and female employees who have no dependent. Write separate SQL statements using "NOT EXISTS" and outer join statements separately for this query. Analyze and compare the two queries' performance in terms of internal memory requirement and the number of operations. (6 points)(Quiz5)
A. NOT EXISTS SELECT AVG(E.Salary) AS AVG_Salary, E.Sex FROM Employee E WHERE NOT EXISTS(SELECT * FROM Dependent D WHERE D.Essn= E.Ssn) GROUP BY E.Sex a. Memory: (200) + (250) Operations: 200 * (1 + (250 * 1))
6. Is in higher normal form always a better relational logical design? Why or Why not? What is a good relational logical design? Explain the specific design considerations including explaining when denormalization will be required for a good relational logical design. (4 points) (Quiz6)
A. No, the design should fit the application. B. Good relational design is determining the best normal form for the design and functionality. C. Denormalization is required for good logical design when the relation will be read only, that is no expected updates, and there is a need to reduce join operations.
4. What are the objectives of database approach/design? (1 point)(Quiz1)
A. One Fact In One Place
1. The major differences between a file-based approach and a database approach are (3 points)(Quiz1)
A. Program Data Independence B. Multiple Views of Data C. Multi-user Transaction Processing
7. SQL is developed for _______ DBMS. (1 pt each)(Quiz1)(Quiz2)
A. Relational
4. Determine the highest normal form (your learned so far) of the following SALES relation and detail how you arrived at your answer, meaning explain the rules. If SALES relation is not in 3rd Normal form, propose a solution to derive a 3NF design. (3 points) SALES (Customer_ID, Customer_Name, Street_Address, State, City, Zipcode, Phone, Credit_card#, Sales_amount) FDs: Customer_ID -> Customer_Name Customer_ID -> Phone Customer_ID -> Credit_card# Customer_ID -> Sales_amount Customer_ID -> Street_Address, State, City Street_Address, State, City -> Zipcode (Quiz6)
A. SALES is in 2nd Normal form. a. It is in 1st Normal form since it has no multi-value attributes, no composite attributes, and no repeating groups. b. It is in 2nd Normal form since it has no partial dependencies. c. It is not in 3rd Normal form because it has transitive functional dependencies. B. Normalize SALES into 3rd Normal form by decomposing on FDs: Customer_ID -> Street_Address, State, City; Street_Address, State, City -> Zipcode a. SALES_A(Customer_ID, Customer_Name, Street_Address, State, City, Phone, Credit_card#, Sales_amount) b. SALES_B(Street_Address, State, City, Zipcode)
3. Write a subquery for the following question: For every project located in 'Stafford', list the controlling department number, and the number of employees working for the project. (2 points) 01. Is the query correlated or non-correlated? ________________________ (1 point) 02. Provide the performance analysis (memory required and number of the operations) for this query. (2 points)(Quiz4)
A. SELECT Dnum, Pno, COUNT(*) AS Number_Of_Employees FROM Project, Works_ON WHERE Pno=Pnumber AND Pno IN (SELECT Pnumber FROM Project WHERE Plocation="Stafford") GROUP BY Dnum, Pno; 01.A. Non-Correlated Query 02.A. Memory: 20 * 800 02.B. Operations: (20 * 800) + (20 * 1)
8. Use subquery: Find all the people (their names) that work for the department where the person with the highest salary of the company works. (3 points) Is the query correlated or non-correlated? (1 point)(Quiz4)
A. SELECT Fname, Minit, Lname FROM Employee WHERE Dno=(SELECT Dno FROM Employee WHERE Salary=(SELECT MAX(Salary) FROM Employee)) B. Non-Correlated
6. What does SQL stand for?(Quiz2)
A. Structured Query Language
2. What is relational logical design? What are the two approaches (with description) for relational logical design? (3 points) (Quiz6)
A. The grouping of attributes to form good relations that will avoid update anomalies, reduces join operations, and improves data retrieval. a. Top Down Approach: 1. Transform ER diagram from conceptual design into a logical relational data model, which consists of tables with enforced constraints. Method used in top-down approach is Denormalization. b. Bottom Up Approach: 1. Breaks down or decomposes large single table into smaller normalized tables with the purpose of avoiding update anomalies. Method used is Normalization.
7. What is the purpose of physical design of a relational database? What are the tasks of physical database design? (Show at least 4 major tasks). (5 points) (Quiz6)
A. The purpose of physical design is to improve performance. a. Tasks of physical database design include: 1. Sub-query fine tuning 2. Storage 3. Indexing 4. View Materialization
13. What does VSAM stand for: (Quiz6)
A. Virtual Storage Access Method
8. Circle all that apply: (no partial credits) (1 point each) (Quiz6)
B-tree is a. binary tree; b. balanced tree; c. each note has same number of children; d. has access length of 1; e. bad at handling insertion; f. usually kept short and fat. A. F, Usually kept short and Fat
5.B. Compute the average of salary for male and female employees who have no dependent. Write separate SQL statements using "NOT EXISTS" and outer join statements separately for this query. Analyze and compare the two queries' performance in terms of internal memory requirement and the number of operations. (6 points)(Quiz5)
B. JOIN SELECT E.Sex, AVG(E.Salary) AS AVG_Salary FROM Employee E LEFT JOIN Dependent D ON E.Ssn=D.Essn WHERE D.Essn IS NULL GROUP BY E.Sex a. Memory: (200 * 250) Operations: (200 * 250) * (1 + 1)
20. Use DFD to describe the following business activity called Appointment in a Dental office: A patient calls to make an appointment, giving his/her names and when he/she would like the appointment to be. The reception will first check the available date/time for the time period (wanted from the patient) from the appointment book. If an appointment time available is agreed with the patient, an appointment will be made and enter to the appointment book. (Quiz2)
EXPLAINED IN CLASS
3. Given EMPLOYEE table has 10 attributes 200 tuples and DEPENDENT Table has 4 attributes and 180 tuples with referential integrities unchanged. Write a SQL query for finding all info of the employees who have dependents. This type of join is called ___________. How many attributes and how many tuples will be in the resulting relation after a natural join operation answering this question is performed? # of attributes:______________, # of tuples:_______________. (5 points)(Quiz3)
SELECT * FROM Employee, Dependent WHERE ssn=essn; EQUIJOIN 13 Attributes, 180 Tuples
3. Use subqueries to write the following query and determine whether it's correlated or non-correlated. Indicate its internal memory required and the processing time (evaluations needed). points): Find the full names of all employees who either is of the same gender of his supervisor or have the same birthday as one of the employee. (4 points)(Quiz5)
SELECT E.Fname, E.Minit, E.Lname FROM Employee E WHERE E.Sex IN (SELECT S.Sex FROM Employee S WHERE S.Ssn=E.Superssn) UNION SELECT E.Fname, E.Minit, E.Lname FROM Employee E WHERE E.Bdate IN(SELECT O.Bdate FROM Employee O WHERE E.Ssn<>O.Ssn) B. Correlated C. Memory: (200 + 200) + (200 + 200) Operations: (((1 x 200)+1) x 200)) + (((1 x 200)+1)x200))
4. Find the employees' last names who work on all the projects controlled by department #3. (5 points). Determine whether it's correlated or non-correlated. (6 points)(Quiz5)
SELECT E.Lname FROM Employee E WHERE NOT EXISTS(SELECT * FROM Works_On B WHERE B.Pno IN (SELECT Pnumber FROM Project WHERE Dnum=3) AND NOT EXISTS(SELECT * FROM Works_On C WHERE C.Pno=B.Pno AND C.Essn=E.Ssn)) Correlated
Write a SQL statement, using the Employee_no_dependent_view to create a report on the total number and highest salary of male and female employees with no children. (QUIZ 7)
SELECT Sex, COUNT(*) AS Total_Employee, Salary FROM Employee_no_dependent_view E, Dependent WHERE Ssn=Essn AND Relationship<> "Son" OR Relationship<>"Daughter" AND Salary IN (SELECT MAX(Salary) From Employee_no_dependent_view) GROUP BY Sex, Salary
What is the relationship between relational algebra & SQL
SQL implements relational algebra and on top of that offers aggregation functions, such as minimal, averages, etc.
5. What year is SQL1 first standardized? _______________ What year is SQL2 first standardized? ________________ What year is SQL3 first standardized? ________________ What year was ER model first proposed?____________ by ___________ What year was relational model first proposed? ________by ______ Spell out SQL _____ and ER_____model.(Quiz2)
SQL1 first standardized 1986 SQL2 first standardized 1992 SQL3 first standardized 1999 ER model first proposed by Peter Chen in 1976 Relational model first proposed by E.F. Codd in 1970, SQL: Structured Query Language, ER: Entity Relationship
Two types of operations in relational algebra
Simple Relational Operation: operated on a row- selection operation, sigma operated on a column- projection operation, pi operated on the link between 2 tables - joint operation, bow tie Set Operation
Name two other common techniques: ___________________________________ and ________________________ that utilizes query trees.
Symantic Query Optimization Heuristic Rules (QUIZ 8)
Compare and contrast: System Catalogue vs. Data Dictionary
Systematic Catalogue: stores data that describes each database, such as meta-data & meta-database. Includes data dictionary Data Dictionary: part of the system catalogue under meta-database. More general: documents the design process and admin information. (QUIZ 8)
The query optimization technique that utilizes application logics/rules/meanings is called _______________.
Systematic Query Optimization (QUIZ 8)
17. Write a SQL statement to give all female employees who has no dependent a 10% raise. (QUIZ 7)
UPDATE Employee E SET Salary = Salary * 1.1 WHERE E.Sex = 'F' AND NOT EXISTS (SELECT * FROM Dependent D WHERE E.Ssn=D.Essn);
What kind of views are updatable?(QUIZ 7)
Views based on single base table
7. Define a data model, detailing the three concepts (or elements) that every data model must contain. (2 points)(Quiz2)
a. A set of concepts to describe the Structure of a DB (data types, relationships), Operations for manipulation of the DB, and Constraints on the DB.
6. What specifics about relational data model that make relational databases most popular for business processing? (1.5 point)(Quiz2)
a. Based on Simplistic Set Theory b. Handles Large Volumes of Similar Data c. Easy to Design
8. Entity Relationship Model is a 1) conceptual 2) logical 3) DBMS-dependent 4) DBMS-independent data model 5) based on Set theory. Circle the correct one(s). (1 point)(Quiz2)
a. Conceptual, DBMS independent
4. Logical data model is a. DBMS-dependent or b) DBMS-independent? ( circle one; 0.5 point)(Quiz2)
a. DBMS dependent.
14. What are the 4 types of a DBMS Language? Spell out the acronyms. What type of the four is SQL as a language for relational DBMS? Which generation language is SQL? What's the characteristic of this GL? (4 points)(Quiz2)
a. Data Definition Language b. Data Manipulation Language c. View Definition Language d. Storage Definition Language e. SQL = DML, 4th GL, High Level Non-Procedural
10. What is the purpose of the ANSI-standard 3-schema (DBMS) Architecture? (1 point)(Quiz2)
a. Describes Data Independence.
1. Give two examples where DBMS or database approach is not advantageous. (1 point)(Quiz2)
a. If the database and applications are simple, well defined, and not expected to change. b. If there are stringent real-time requirements that may no be met because of DBMS overhead. c. If access to data by multiple users is not required.
15. What is the difference between 2-tier vs. 3 tier client-server architecture for database system deployment ? (1 point)(Quiz2)
a. In two tier the client and database server communicate directly. b. In three tier there is an intermediary, an application or web server that communicates between the client and the database server.
16. What are four characteristics of data warehousing that is different from the operation data store (regular database)? (2 points)(Quiz2)
a. Integrated b. Read Only / Non-Volatile c. Historical d. Subject Oriented
13. Compare and Contrast the following concepts:(Quiz2)
a. Intension vs. Extension (2 points) i. Intension is the structure of the database. Extension is a valid state of the database at certain point in time. b. SQL Server vs. SQL server (2 points) i. SQL server is the general term for an SQL server. ii. SQL Server is Microsoft's SQL server product. c. Conceptual design vs. Logical design i. Conceptual Design is DBMS independent, while Logical Design is DBMS dependent.
17. Describe the process of DB design following a RAD methodology. (4 points) Explain the tasks to be done and associated tools in each phase. (3 points)(Quiz2)
a. Iterative Design process i. Requirement Analysis 1. Minimum requirements: transactions, reports, queries ii. Conceptual Design: 1. Collect and formulate entire content of the database into a user friendly model. Completeness of the database. 2. DFD, UML iii. Selection of DBMS: 1. Justifications, Logical Model, Size, Data Type, Support, ... iv. Logical Design: 1. Organize conceptual design into logical design. (Table - Relational) 2. Normalization Theory, De-Normalization. v. Physical Design: 1. Database Performance. vi. Implementation: 1. UI or Application system to access database. 2. Implement DB system vii. Performance Evaluation: 1. Testing 2. User Survey 3. Future Requirements for next iteration phase 4. Go Back to Requirements.
19. What are the 4 constraints of relational data model? (2 points)(Quiz2)
a. Key b. Domain c. Entity Integrity d. Referential Integrity
2. Give two major DBMS functions. (0.5 point each)(Quiz2)
a. Multiple user interfaces b. -controlled redundancy c. -integrity control d. -security: authorization & protection e. -concurrency & recovery control
3. What are the logical data models in addition to relational? (name at least 3) (1.5 points)(Quiz2)
a. Network, Object-Oriented, Hierarchical
18. What are the 4 constructs of DFD? (2 points)(Quiz2)
a. Source/Sink : Input/Output, Depicted as a box b. Process : Depicted as an Oval c. Data Flow : Depicted as an Arrow d. Data Store / File / Database : Depicted as two horizontal parallel lines.
9. Define a database schema. (1 point).(Quiz2)
a. The description of the database, including structure, data types, and constraints. Intension.
2. a. Give the result of the following SQL Query. (4 points) b. The operation performed by this query is called __________________________ (1 point) SELECT * FROM AA, BB; AA(C_ID, Name, Ad) BB(O_ID, P_ID, C_ID) 2 x a 100 P2 2 4 y b 300 P2 2 9 z c (Quiz3)
a: b: Cartesian Product
What is relational algebra?
defines the operation on a relational database.
Use Employee data, create a view called Employee_no_dependent_view where every employee has no dependent. The view should have all the attributes of Employee and Dependent relations (QUIZ 7)
• CREATE VIEW Employee_no_dependent_view AS SELECT E.*, Essn, Dependent_Name, D.Sex AS D_Sex, D.Bdate AS D_Bdate, Relationship FROM Employee E LEFT OUTER JOIN Dependent D ON E.Ssn=D.Essn WHERE D.Essn IS NULL
What is a view? (QUIZ 7)
• A specific construct known as a virtual table, it does not exist on a physical level and it is not a base table. It is based off of base tables to facilitate data retrieval.
Which is/are the correct statement(s)?
• A. A record can exceed the size of a block. • C. A block is the unit of transmission between main memory and secondary storage.
Which of the following concepts is/are relevant to recovery control?(QUIZ 7)
• ACID • Checkpoint • Shadow Paging • Periodical Dump
1. ACID property of transaction processing stands for:(QUIZ 7)
• Atomicity • Consistency • Isolation • Durability
16. Write a SQL statement to create the Employee table, with the Foreign Keys and primary key specified. (QUIZ 7)
• CREATE TABLE Employee ( FNAME VARCHAR(50), MINIT VARCHAR(50), LNAME VARCHAR(50), SSN VARCHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(50), SEX VARCHAR(1), SALARY INT, SUPERSSN VARCHAR(9), DNO VARCHAR(2) NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (DNO) REFERENCES Department (Dnumber), FOREIGN KEY (Superssn) REFERENCES Employee (Ssn) );
Which of the following indexes are ordered based on non-key attribute(s)?(QUIZ 7)
• Clustering • Secondary Indexes
What is view materialization? (QUIZ 7)
• Creates a physical view table.
Which is/are based on granting and revoking privileges?(QUIZ 7)
• Discretionary Access Control
Write a SQL statement to give employee Tiger the privilege of SELECT on Employee table and to allow Tiger to give privilege to Lion to update Employee salary. (QUIZ 7)
• GRANT SELECT, UPDATE (Salary) ON Company.Employee TO 'Tiger' @ 'localhost' WITH GRANT OPTION;
2. What are the three concurrency control techniques(QUIZ 7)
• Locking, with problems of deadlocks • Timestamp Ordering, with problem of cascading rollbacks • Optimistic Concurrency Control, which involves a Validation phase after read phase before write phase.
12. Draw or describe the 3-schema Architecture. Use the architecture to explain logical data independence and physical data independence. (4 points)(Quiz2)
• Logical data independence: the external views (applications) are immune to change in the conceptual level and physical level schema • Physical data independence: the external views (applications) and conceptual level schema (conceptual and logical schema) are immune to change in physical level schema.
Which is the algorithm of Bell-laPadula model for? (QUIZ 7)
• Mandatory Access Control
Describe 3 common techniques used for statistical database security control. (QUIZ 7)
• No statistical queries are permitted whenever the number of tuples in the population specified by the selection condition falls below some threshold • Prohibit sequences of queries referring repeatedly to the same population • Insert noise
Which design phase of the database design process does view materialization occur? (QUIZ 7)
• Physical Design Phase
Which of the following indexes are non-dense (sparse)?(QUIZ 7)
• Primary Index • Clustering
Which of the following concepts is/are relevant to (or used for) concurrency control?(QUIZ 7)
• Serializability Theory • Granularity • Rollback
5. In situation where a lower priority transactions has been repeated stopped from being complete because higher priority transaction were given all the resources is called _____________. (QUIZ 7)
• Starvation
What are the four database security mechanisms/techniques? (QUIZ 7)
• Statistical Database Security Control • Views • Discretionary Access Control (DAC) • Mandatory Access Control (MAC)
Compare and contrast two phase locking and two phase commit. (QUIZ 7)
• Two phase locking means that all locking operations must precede the first unlock operation in the transaction. Two phase locking has an Expanding Phase (growing/locking) and a Shrinking Phase (unlocking) • Two phase commit means that all processes in the transaction must signal it is ok to commit before the commit can proceed. Phase 1: Prepare to commit, Ready to commit. Phase 2: OK to commit, commit.
3. What is deadlock?(QUIZ 7)
• Two processes competing for the same resources each holds a portion of those resources and is waiting for the other to release the resources the other is holding. Since each process is waiting for the other to release its resources, each process will wait indefinitely, thus a deadlock.
4. What is livelock?(QUIZ 7)
• Two processes competing for the same resources, each holding resources the other needs, will release some of the resources (breaking a deadlock), but will then request for other competing resources creating new deadlocks, thus creating a cycle of breaking deadlocks and making new deadlocks.