Midterm Exam Note
.docx
keyboard_arrow_up
School
Arizona State University *
*We aren’t endorsed by this school
Course
511
Subject
Computer Science
Date
Jan 9, 2024
Type
docx
Pages
2
Uploaded by ChiefPheasantPerson1012
Given two tuple sets (A with 10 elements and B with 5 elements), what is the maximum number
of tuples in the union
of these sets?
A) 15
Which mathematical operator must be used in order to display information about students who are majoring in Computer Science but not
in Electrical Engineering? A) difference
Consider a relation, STUDENT, with the following attributes: Name, ID number, Major, Graduation Year, and GPA. Consider a query using this relation: “List the names and ID numbers of students
majoring in Computer Science.” Which
operator is absolutely necessary for this query? A) projection
Consider two sets: A with 10 tuples, and B with 5 tuples. What is the
maximum number
of tuples that the intersection
of A and B can have? A) 5
Which statement(s) about the selection operator is correct? Select all that apply.
A) it displays certain tuples based on given criteria
D) the selection operator can work on a relation even with a single attribute
Which query is executed first in a nested subquery?
A) the nested query runs first, returning values to the main query
In an ER Diagram, a diamond shape indicates what? A) A relationship
What does the relational algebra operator ‘x’ indicate? A) All possible pairs from the two tables
Which of the following two operators must be used one after another? A) Cartesian Product, ϴ-Join
Which operator must be used to display information about students majoring in Mathematics or
Physics?
A) Union
Which mathematical operator must be used in order to display information about students who are majoring in both Computer Science and
Electrical Engineering? A) Intersection
Which of the following is/are among the disadvantages of indexes? Select all that apply.
A) Increase in the cost of storage
B) Increased cost of maintenance
User of the items in the memory hierarchy, how many are present directly on the CPU die?
A) 2: registers and L1 cache
If the expected use case for a file is retrieving a range of records
, which file organization should be used?
A) Sorted files
Building an index on a heap file will probably result in what kind of index? A) Unclustered
Index files are characterized by what kind of index(es)? Select all that apply.
A) Tree
C) Hash function
A heap structure of a file is characterized by what structure? A) Linked pages that are appended without sorting
Which of the following database operations does not require
a transaction to obtain an exclusive lock
? A) Read data
Which of the following describes the isolation
principle of database transactions? A) Each transaction runs as if there is
no other transaction running in the database system simultaneously.
Which of the following describes the durability
property of database transactions? A) Once a transaction commits, the system guarantees the results will never be lost, in spite of subsequent failures.
What is/are the phrase(s) involved in Recovery algorithm? Analysis, Redo, Undo
Why is it important to have concurrency control in databases? A) Concurrency Control is needed so that data can be updated correctly when multiple transactions are executed concurrently.
Which of the following describes the serializability
property of database transactions? A) If several transactions are executed concurrently, the results must be the same as if they were executed one after another.
Identify the difference between a data model and a schema
●
The data model is a collection of concepts for describing data
●
Schema is a description of a particular collection of data, using a given data model
Define how a data model and schema relate
●
They define how data points are organized and connected within a Relational Database, including logical constraints.
State and Describe the three levels of abstraction
●
View - represents the user’s view of the database such as pulling the data from the table
●
Logical (Conceptual) - define the logical structure of the database independence and build a table
●
Physical - its database handles the sorted file and index to organize the data pages.
Explain the advantages and disadvantages of using a database management system
●
Advantages: Data Independence, Security, Access Control, Data Recovery, and Backup
●
Disadvantages: Cost and Complexity, Potential Single Point of Failure
Outline the correct order of execution for the phases of designing a database → Gather the requirements, Conceptual Design (Build ER), and Logical Design (Convert ER into a relational model).
Define an entity → Speaking of ER Design, the entity is a set of attributes. For example, let's say, Student, so we should create different objects for Student such as FirstName, LastName, Gender, and so on.
Provide examples of different, possible attributes that can be used as a key for an entity and describe why: → SSN, VIN, studentID, id
Define the following relationship types
●
Multiple Relationship
○
There are two entity types where they can connect to each other as long as they wish to connect many. For example, students can enroll in many courses so many courses are available to students who want to enroll,
●
Binary Relationship
○
There are two entity types where they can connect once. For example, a person wants to follow another person on social media (e.g. Twitter).
●
Unary Relationship
○
It is like a recursive relationship. For example, the employee indicates the supervisor-subordinate relationship.
●
Ternary Relationship
○
There are three distinct types of relationships. For example, there is a hospital database between Doctors, Patients, and departments.
Consider the following two sets - A with 50 tuples and B with 10 tuples What is the maximum number of tuples that the set (A intersection B) have? A (50 intersection 10) B = 10!
Define the function of each of these commands
●
SELECT → Get the columns or attributes by specifying the desired columns
●
FROM → Specifying the tables
●
WHERE → It is a condition where I can filter and select the specific rows.
Describe each of the terms
●
Union → Union combines the results of two or more SELECT statements into a single result set.
●
Intersection → Intersection returns the common rows between two or more SELECT statements.
●
Cartesian Product →It is like a cross join, so it combines every row from one table with every row from another table
●
Difference → MINUS or EXCEPT retrieves the rows from one SELECT statement that do not exist in another SELECT statement.
List the main tasks of the Buffer Manager
●
Buffer Allocation and Management, Disk I/O, Caching, and Performance Optimization
Identify the advantages and disadvantages of using Heap Files for storage
●
Advantages → Simplicity, fast insertion, flexible data access
●
Disadvantages → Slow retrieval, fragmentation
Describe the purpose of leaf nodes and non-leaf nodes in B+ tree
●
Leaf nodes → Contain the actual data entries or records of the database or indexed data and holds a range or block of data entries and their associated key values
●
Non-leaf Nodes → Store index or key values that guide the search process
Define each option and their purpose
●
Indexes → Used to improve the efficiency of data retrieval & Allows for faster access to specific data
●
Sorted files → Organize data records in a specific order based on one or more key attributes
●
Heap files → Store data records in an unordered manner without any specific organization or sort of order
●
Randomly access files → Allow direct and immediate access to specific records based on their unique
address or identifier.
State what form entities are converted to when a relational DB from an ER diagram is created
●
They are transformed into tables in the relational database model. Each entity in the ER diagram corresponds to a table in the relational database.
Explain the difference between clustered index and unclustered index
●
Clustered index → The physical order of the data in the table corresponds to the order of the index
●
Unclustered index → Its physical database is not ordered based on the unclustered index
Define and explain ACID properties of transactions
Atomicity
- Its transaction is considered atomic if all its operations are executed as a whole, either entirely or not at all
Consistency
- If a transaction violates any integrity constraints, the changes made by the transaction are rolled back, ensuring that the database remains in a consistent state
Isolation
- Concurrently executing transactions should not interface with each other, preserving the data integrity and consistency
Durability
- Even in the event of power loss or system crashes, the changes made by committed transactions are durable and can be recovered when the system restarts.
Explain the mechanism of the Write-Ahead Log Protocol → WAL protocol ensures the atomicity and durability of transactions by following the strict order of writing the log before modifying the database.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Questions
Write relation algebra queries for following:Student( Enrno, name, courseId, emailId, cellno)Course(courseId, course_nm, duration)i) Add a column city in student table.ii) Find out list of students who have enrolled in “computer” course.iii) List name of all courses with their duration.iv) List name of all students start with “a”.v) List email Id and cell no of all mechanical engineering students.
arrow_forward
Suppose relations R(A,B) and S(B,C,D) have the tuples shown below:
R
A B
5
3 4
46 8
479
Compute the result of the join query:
SELECT A, R.B, S.B, C, D
FROM R, S
WHERE R.A
arrow_forward
Write relation algebra for following:Student( Enrno, name, courseId, emailId, cellno)Course(courseId, course_nm, duration)i) Add a column city in student table.ii) Find out list of students who have enrolled in “computer” course.iii) List name of all courses with their duration.iv) List name of all students start with “a”.v) List email Id and cell no of all mechanical engineering students.
arrow_forward
Code three tests:
1) for a student who has courses
2) for student who does not have courses
3) a non-existing student ID. For each test, display the value of the student ID that was passed to the function and the result returned by the function. Also, run a supportive SELECT query or queries that prove the test results are correct.
arrow_forward
True or False below:
Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B).
* in an SQL SELECT statement means “all the tuples”.
In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true.
SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.
arrow_forward
grade varchar(30),
salesman_id int,
CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id)
);
insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3002, 'Nick Rimando', 'New
York', 100, 5001);
insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3007, 'Brad Davis', 'New York',
200, 5001);
insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3005, 'Graham Zusi',
"California', 200, 5002);
insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3008, Julian Green', "London',
300, 5002);
insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3004, 'Fabian Johnson',
'Paris', 300, 5006);
insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3009, 'Geoff Cameron',
"Berlin', 100, 5003);
insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3003, 'Jozy Altidor, 'Moscow',
200, 5007);…
arrow_forward
please help me to write the expression for these in access. please.
arrow_forward
rtjtyjtyjty
arrow_forward
sdvd
arrow_forward
cvbxc
arrow_forward
Q9: You are asked to normalize the following Table into 3rd Normal Form.
position salary Hours
Worked
Staff name
No
$1500 Tom Daneils Supervisor
S0003 Sally Adams Manager
S0010 Mary Martin Analyst
$1500 Tom Daneils
$2250 Robert Chin
S0415 Sally Stern
S3250 Art Peter
S0010 Mary Martin
S0415 Sally Stern
a.
b.
C.
d.
e.
Which of the following relations will NOT be part of the solution in the 3nd
Normal Form?
position
Toy
staffNo
Alien
Alien
Alien
Car
Car
branch No
branch No
staffNo
Products Table
3500 20
Supervisor 4500 24
Manager 5000 11
Engineer 3000 14
4500 30
Supervisor
Analyst
3500 23
Engineer 3000 21
Engine
Engine
4500 12
5000 10
Part
salary
ন
HoursWorked
branchAddress
name
branchTelNo
Branch Branch
No
Address
B001
B001
B001
B004
8Th road, London, UK
8th road, London, UK
8th road, London, UK
5th road, Leeds, UK
Central London, UK
Central London, UK
Avenue, Manch, UK
5th road, Leeds, UK
B002
B002
B003
B004
B003 Avenue, Manch, UK
Q10: Based on the following TWO Tables (Products and…
arrow_forward
In Sql Oracle Apex application, Using GROUPING SETS, write a query to show the following groupings:• department_id, manager_id, job_id• manager_id, job_id• department_id, manager_id
arrow_forward
Do not run the query. I need explanation of what this query is returning. Number of tuple returned by this query are?
arrow_forward
Specify the initial canonical query tree that corresponds to each query.
1) SELECT AVG(CUSTOMER.AGR) AS "AVG_AGE" FROM CUSTOMER;
2) SELECT * FROM VEHICLE WHERE V_BRAND LIKE 'L%';
3) SELECT MEDIAN(VEHICLE.YEAR_OF_MADE) AS "MEDIAN_YEAR" FROM VEHICLE ;
arrow_forward
What is the difference between a UNION query and an EXCEPT (MINUS) query?
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Related Questions
- Write relation algebra queries for following:Student( Enrno, name, courseId, emailId, cellno)Course(courseId, course_nm, duration)i) Add a column city in student table.ii) Find out list of students who have enrolled in “computer” course.iii) List name of all courses with their duration.iv) List name of all students start with “a”.v) List email Id and cell no of all mechanical engineering students.arrow_forwardSuppose relations R(A,B) and S(B,C,D) have the tuples shown below: R A B 5 3 4 46 8 479 Compute the result of the join query: SELECT A, R.B, S.B, C, D FROM R, S WHERE R.Aarrow_forwardWrite relation algebra for following:Student( Enrno, name, courseId, emailId, cellno)Course(courseId, course_nm, duration)i) Add a column city in student table.ii) Find out list of students who have enrolled in “computer” course.iii) List name of all courses with their duration.iv) List name of all students start with “a”.v) List email Id and cell no of all mechanical engineering students.arrow_forwardCode three tests: 1) for a student who has courses 2) for student who does not have courses 3) a non-existing student ID. For each test, display the value of the student ID that was passed to the function and the result returned by the function. Also, run a supportive SELECT query or queries that prove the test results are correct.arrow_forwardTrue or False below: Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B). * in an SQL SELECT statement means “all the tuples”. In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true. SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.arrow_forwardgrade varchar(30), salesman_id int, CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id) ); insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3002, 'Nick Rimando', 'New York', 100, 5001); insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3007, 'Brad Davis', 'New York', 200, 5001); insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3005, 'Graham Zusi', "California', 200, 5002); insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3008, Julian Green', "London', 300, 5002); insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3004, 'Fabian Johnson', 'Paris', 300, 5006); insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3009, 'Geoff Cameron', "Berlin', 100, 5003); insert into customer (customer_id, cust_name, city, grade, salesman_id) values (3003, 'Jozy Altidor, 'Moscow', 200, 5007);…arrow_forwardcvbxcarrow_forwardQ9: You are asked to normalize the following Table into 3rd Normal Form. position salary Hours Worked Staff name No $1500 Tom Daneils Supervisor S0003 Sally Adams Manager S0010 Mary Martin Analyst $1500 Tom Daneils $2250 Robert Chin S0415 Sally Stern S3250 Art Peter S0010 Mary Martin S0415 Sally Stern a. b. C. d. e. Which of the following relations will NOT be part of the solution in the 3nd Normal Form? position Toy staffNo Alien Alien Alien Car Car branch No branch No staffNo Products Table 3500 20 Supervisor 4500 24 Manager 5000 11 Engineer 3000 14 4500 30 Supervisor Analyst 3500 23 Engineer 3000 21 Engine Engine 4500 12 5000 10 Part salary ন HoursWorked branchAddress name branchTelNo Branch Branch No Address B001 B001 B001 B004 8Th road, London, UK 8th road, London, UK 8th road, London, UK 5th road, Leeds, UK Central London, UK Central London, UK Avenue, Manch, UK 5th road, Leeds, UK B002 B002 B003 B004 B003 Avenue, Manch, UK Q10: Based on the following TWO Tables (Products and…arrow_forwardIn Sql Oracle Apex application, Using GROUPING SETS, write a query to show the following groupings:• department_id, manager_id, job_id• manager_id, job_id• department_id, manager_idarrow_forwardarrow_back_iosSEE MORE QUESTIONSarrow_forward_ios
Recommended textbooks for you
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrProgramming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning