Final Exam study Flashcards

Question

What is BCNF, 4NF, or 5NF

Answer 1

BCNF (Boyce-Codd Normal Form): A stricter version of 3NF. For any non-trivial functional dependency A→ Y, A must be a superkey. 4NF (Fourth Normal Form): Removes multi-valued dependencies. A table is in 4NF if it's in BCNF and has no multi-valued dependencies. 5NF (Fifth Normal Form or PJNF): Ensures data can be recomposed from sub-tables without loss or redundancy. A table is in 5NF if it's in 4NF and, for every non-trivial join dependency, the intersection of each pair is a superkey.

Answer 2

To display all the name of bus stop locations that is used as the starting point of a bus route.

Answer 3

None of the options are correct.

Answer 4

σQuantity > 5 (Product ⋈Product.ProductID = Order.ProductID Order)

Answer 5

The Student table may contain more than one student with the same age.

Answer 6

Property JOIN Owner USING(OwnerID)

Answer 7

The correct answer is: None of these options are correct.

Answer 8

The correct answer is: A cursor handler is not compulsory

Answer 9

CALL Mystery7(5, @c);

Answer 10

ΠProductName, Price (Product)

Answer 11

Property JOIN Owner USING(OwnerID)

Answer 12

CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Age INT );

Answer 13

INSERT INTO table_name (column1, column2, ... columnN) VALUES (value1, value2, ... valueN);

Answer 14

INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (1, 'John', 'Doe', 20);

Answer 15

It is "--"

Answer 16

if the database is called "Database1" then the syntax is "use Database1"

Answer 17

Type the following: SELECT 'information' From databasename;

Answer 18

-Returns rows when there is a match in both tables. -If there's no match, the row is not included in the result.

Answer 19

In SQL, a "LEFT JOIN" (or "LEFT OUTER JOIN") is a type of join that returns all records from the left table (the one that precedes the JOIN keyword), and the matched records from the right table (the one that follows the JOIN keyword). If there is no match, the result is NULL on the side of the right table.

Answer 20

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Answer 21

SELECT Table1.name, Table2.age FROM Table1 LEFT JOIN Table2 ON Table1.id = Table2.id;

Answer 22

In SQL, a "RIGHT JOIN" (or "RIGHT OUTER JOIN") is a type of join that returns all records from the right table (the one that follows the JOIN keyword), and the matched records from the left table (the one that precedes the JOIN keyword). If there is no match, the result is NULL on the side of the left table. The basic syntax for a RIGHT JOIN is similar to that of a LEFT JOIN, but the tables are switched in terms of their position related to the JOIN keyword:

Answer 23

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Answer 24

In SQL, the UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types. The columns in each SELECT statement must also be in the same order.

Answer 25

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; For example, if you have two tables, table1 and table2, that both contain a column for name, you could combine the names from both tables while removing duplicates with the following query: SELECT name FROM table1 UNION SELECT name FROM table2; If you want to include all duplicates, you would use UNION ALL: SELECT name FROM table1 UNION ALL SELECT name FROM table2;

Answer 26

A database stored program is a computer program that is stored and executed within the database server.. It contains a series of instructions written in mySQL procedural language. Each stored procedure is associated with a name. The source code and (sometimes) any compiled version of the stored program are always held within the database servers system tables. When the program is executed, it is executed within the memory address of a database server, process or thread. This is also called a stored module or a stored routine.

Answer 27

In MySQL, the DELIMITER command is used to change the standard delimiter, which is the semicolon (;), to another sequence of characters. This is particularly useful when you want to define stored procedures, functions, triggers, or other database objects that contain multiple SQL statements. An example of this is as follows: DELIMITER // CREATE PROCEDURE myProcedure() BEGIN -- Procedure definition goes here, semicolons can be used without ending the procedure SELECT 'Hello, World!'; END// DELIMITER ;

Answer 28

The SELECT INTO statement in SQL is used to select data from one table and insert it into a new table. This can be very useful when you need to create a backup copy of a table or to extract a subset of data for further processing.

Answer 29

- Locking - Timestamping Both are conservative approaches: delay transactions in case they conflict with other transactions.

Answer 30

A transitive dependency in a database occurs when one non-prime attribute is dependent on another non-prime attribute via a transitive relation. In simpler terms, this means that attribute A depends on attribute B, which then depends on attribute C, where C is a candidate key (a column or set of columns that can uniquely identify any database record without referring to any other data), and A and B are not candidate keys. Here's an example to illustrate transitive dependency: Consider a table with three columns: StudentID, Class, and TeacherName. - StudentID is a primary key, so each record is unique to a student. - Class is determined by the StudentID (a student takes a specific class). - TeacherName is determined by the Class (a specific teacher teaches a specific class). In this case, TeacherName has a transitive dependency on StudentID through Class. TeacherName does not depend directly on StudentID; it depends on which Class the student is taking. According to 3NF, this transitive dependency should be removed to prevent anomalies and redundancy in the database. This is typically done by splitting the table into two: A Students table with StudentID and Class. A Classes table with Class and TeacherName. Now, TeacherName is directly dependent on Class, which is a candidate key in the Classes table, and the transitive dependency is resolved. This normalization process helps ensure that each table contains data related only to a single concept or entity, reducing redundancy and improving data integrity.

Answer 31

1..1 (One-to-One): An entity on one side of the relationship is associated with exactly one entity on the other side. For example, each person has exactly one passport, and each passport is assigned to exactly one person. 0..1 (Zero or One-to-One): An entity on one side of the relationship is associated with zero or one entity on the other side. For example, a person might have zero or one driver's license. *1.. (One-to-Many)**: An entity on one side (the "one" side) is associated with zero, one, or multiple entities on the other side (the "many" side). For example, a mother (one) has children (many). *0.. (Zero-to-Many)**: An entity on one side does not need to be associated with an entity on the other side, but it can be. For example, a teacher (one) could have zero, one, or many students (many). .. (Many-to-Many): Entities on both sides of the relationship can have zero, one, or many associations to the other side. For example, students (many) can enroll in multiple courses (many), and each course can have multiple students.

Answer 32

Entity integrity is a concept within the realm of relational databases that ensures each row (or record) in a table is uniquely identifiable. This is a critical aspect of database design and integrity constraints, and it is achieved through the proper use of primary keys.

Answer 33

A functional dependency in a relation occurs when one attribute uniquely determines another attribute. This relationship is denoted as A → B, which means "B is functionally dependent on A." If you know the value of A, you can determine the corresponding value of B.

Answer 34

Suppose you have a table like this: Sales Order Table: OrderID (Primary Key) ProductID (Part of Composite Key) OrderDate CustomerID CustomerName ProductName To convert this table into 2NF, we must remove partial dependencies. If ProductID and OrderID together form a composite key, and ProductName is dependent only on ProductID, then ProductName should be moved to a separate table where ProductID is the primary key. After moving to 2NF: Sales Order Table (2NF): OrderID (Primary Key) ProductID OrderDate CustomerID Product Table (2NF): ProductID (Primary Key) ProductName To convert the "Sales Order Table" into 3NF, we must remove transitive dependencies. If CustomerName is dependent on CustomerID (and CustomerID is not a candidate for a primary key of the "Sales Order Table"), then CustomerName should be moved to its own table. After moving to 3NF: Sales Order Table (3NF): OrderID (Primary Key) ProductID OrderDate CustomerID Customer Table (3NF): CustomerID (Primary Key) CustomerName

Answer 35

SELECT S.s_name, S.s_contact, COUNT(DISTINCT BS.b_id) AS brand_count FROM Supplier AS S JOIN BrandSupplier AS BS ON S.s_id = BS.s_id WHERE S.s_state = 'NSW' GROUP BY S.s_id ORDER BY brand_count DESC LIMIT 1;

Answer 36

Yes it is SELECT * FROM BrandSupplier bs JOIN Brand b ON bs.b_id = b.b_id RIGHT JOIN Coffee c ON b.b_id = c.b_id;

Answer 37

Performance should be evaluated on a case-by-case basis, often using the EXPLAIN statement or equivalent in your SQL database to analyze the query execution plan. It's also good practice to consider the readability and maintainability of the SQL queries. For critical performance cases, you should benchmark both methods under realistic data loads and query conditions.

Answer 38

The intended purpose of the query appears to be to retrieve the names and contact information of suppliers from the Supplier table who are either located in the state 'A' or the country 'USA' and have a priority less than 3 in the BrandSupplier table. The ORDER BY s_name clause suggests that the results should be ordered alphabetically by the supplier's name. The query is not well written because the query uses an old style join syntax, its missing its join condition and has ambiguous boolean logic.

Answer 39

The provided stored procedure, named Mystery, seems to have two primary actions: It counts the number of suppliers in the Supplier table and assigns this value to an OUT parameter named something. It then selects the names of all suppliers from the Supplier table and returns this as a result set. The purpose of the something OUT parameter is to hold the count of supplier records in the Supplier table after the procedure is executed. The procedure itself does not do anything with this count within its own body; it simply provides the count to the caller. A more descriptive name for the stored procedure might be GetSupplierCountAndNames, which reflects the actions being taken within the procedure.

Answer 40

Yes, triggers can be used to enforce business rules in a database system. A trigger is a database object that is automatically executed or fired when certain events occur. Triggers are often used to enforce complex business rules that cannot be enforced using the standard constraints provided by the database management system (DBMS), such as CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY constraints Suppose you have a business rule that states "a customer's account balance must never go below zero." This is a rule that you might enforce using a trigger.

Answer 41

DELIMITER // CREATE PROCEDURE insertBrand (IN brandName TEXT) BEGIN -- Declare a variable to hold the new ID DECLARE newId INT; -- Find the maximum brand ID currently in use and add 1 to it to get the new ID SELECT MAX(b_id) + 1 INTO newId FROM Brand; -- Check if the newId is NULL which means the Brand table is empty, then start from 1 IF newId IS NULL THEN SET newId = 1; END IF; -- Insert the new brand with the calculated new ID INSERT INTO Brand (b_id, b_name) VALUES (newId, brandName); END // DELIMITER ;

Answer 42

This occurs when two transactions that access the same data are interleaved in such a way that the modifications made by one transaction are overwritten by another. The update made by the first transaction is lost by the time the second transaction is committed.

Answer 43

This happens when a transaction reads the same row twice and gets a different value each time. This is usually because another transaction has updated or deleted that row after the first read.

Answer 44

A dirty read occurs when a transaction reads data written by a second transaction that has not yet committed. If the second transaction rolls back, the first transaction will have read data that was never officially in the database.

Answer 45

Considering the sequence of operations: Lost Update: This could potentially be an issue with the writes to X by T2 and T4. If T2's first write is considered an update based on a value of X that T1 read, and then T4 writes over it before T2's second write, T2's first update to X could be lost. However, it's not clear from the schedule if T2's first write is indeed an update based on T1's read or an independent write. If it is independent, then there's no lost update. If it's based on T1's read, then the lost update could be present because of T4's subsequent write. Unrepeatable Read: There is no indication that any transaction reads the same value twice, so we cannot confirm the presence of an unrepeatable read in the given schedule. Dirty Read: No transaction is reading data that has been written by a transaction that has not yet committed. T4 writes to X and then commits, and there are no reads of X by other transactions between T4's write and commit. Therefore, there are no dirty reads in the given schedule.

Answer 46

The write-ahead logging (WAL) protocol is a method used in databases to ensure that changes to data are not written to the main storage before the corresponding log record is written to persistent storage. This protocol ensures that the log precedes the actual data changes, enabling the database to recover to a consistent state after a crash by redoing committed transactions and undoing uncommitted transactions using the log. It is crucial for maintaining database integrity and consistency in the event of system failures.

Final Exam study Flashcards

(156 cards)