Database Systems Midterm Exam

From the Company Schema, provide a select statement that returns the employee ssn, last name, project names and hours-worked for the employees with the last name 'Smith'. Hint: This will require a join between three tables. (SPECIAL)

select ssn, lname, pname, hours from employee, works_on, project where employee.ssn = works_on.essn and project.pnumber = works_on.pno and employee.lname = 'Smith'; This explicite join also works: select ssn, lname, pname, hours from (employee join works_on on ssn = essn) join project on pno = pnumberwhere lname = 'Smith';

Describe the properties of attributes that define a Key in a relation. Is Employee[fname, lname, ssn] a key? Describe the difference between a Candidate Key and a Primary Key. Referring to the Company Schema, describe why Employee[fname, lname] would not make an appropriate key. Referring to the Company Schema, why is it impossible for the following statement to update (modify) more than one tuple? UPDATE employee SET address='123 First St.' WHERE `ssn`='2223332222';

A Key is a minimal set of attributes that uniquely identify every tuple. That is, a key is the set of attributes such that if any of the attributes were removed from the key set, the remining set of attributes would no longer uniquely identify every tuple in the relation.Employee[fname, lname, ssn] is not a key because although the set of attributes uniquely every tuple, it is not a minimal set of attributes. A Candidate Key is any Key in the relation. The Primary Key is the Key that that will be used to identify the relation's tuples in foreign key relationships. The definition of a key is that every tuple in the relation must be uniquely identified by the combined values assigned to a relation's keys. In this case, the combined values assigned to first and last name will not uniquely identify every employee as there are many examples in the application's problem domain of two employees with the same first and last names. The employee relation defines the attribute ssn as its primary key. This means that there must be either zero or 1 tuples in the relation with the attribute ssn == 2223332222.

Describe how a client's role is used to protect the database's information from unauthorized access. What drives the determination of what client roles are needed by a schema? Who (i.e. The DBMS Role from section 2) typically creates client (user) accounts and roles in a production database? Provide an example of a role and access restriction from the University schema.

A client's role is associated with its account i.e. ID & password. The client's role is used to determine which objects (tables, views, attributes, etc.) and operations on those objects a client can access. The role restricts the client's access to those operations it needs to fulfill its requirements without unnecessarily exposing the system's data. The roles needed in a schema are dictated by the application requirements driving the overall schema's design. Each requirement may describe specific entities and operations the need to be supported to implement the requirement. The database administrators (DBA) creates accounts for the database server and assigns roles to the accounts. Accounts & roles are used to authenticate access to the database server. One Possible Answer: From the University example, we described a situation where the Student entity maintains sensitive information (e.g. a social security number) which is not visible to the role Instructor but is visible to the role Administrator.

Provide a brief definition of database based on the material in the slides. Provide a brief definition of DBMS based on the material in the slides. What is the relationship between DBMS and database?

A database is a persistent collection of entity instances, their individual attributes, and relationships between the entities. The DBMS is a server used by client application to maintain persistent entity instances. Collections of instances persistently maintained in files on disks. A DBMS maintains and provides access to one or more databases.

Describe the name and meaning of the two sides of a relationship defined by a Foreign Key. Which side of the relationship maintains the foreign key attributes in a 1-N association? Describe two FK relationships from the Company schema. Define the referential integrity constraint imposed by a foreign key.

A foreign key describes a relation attribute(s) that maintains the value of another relation's primary key. The FK defines a relationship between the two relations. The relation that maintains the primary key is the referenced relation. The relation that references the referenced relation is the referencing relation. In the Employee - Department, Employee is the Referencing and Department is the Referenced. The referencing relation maintains the Foreign Key. There are several examples of FK relations in the Company Schema. For example, the relationship between employee (referencing) and department (referenced) is implemented by the FK employee.dno to the primary key department.dnumber. (Grader please check each answers against the Company schema). The FK referential integrity constraint is that a tuple in the referenced table cannot be removed when there exist a tuple in the referencing relation that maintains a reference to the referenced tuple.

Describe the relationship between... Schema and Relation Relation and Entity Relation and Attribute Attribute and Domain Relation and Tuple Tuple and Attribute

A single schema maintains multiple relations. Relations must be uniquely named in the schema. A relation represents an entity from the problem domain. Relation is the formal name given to Table. Each row in a relation represents one instance of an entity (table) or one instance of a relationship between entities (join-table). A relation maintains multiple attributes. These are the entity's attributes or table's columns). Attributes must be uniquely named in the relation. Each attribute is a member of a Domain. Domains describe the meaning and allowed values that can be assigned to the attribute. Domains equivalent to data types in programming languages. A relation maintains multiple tuples. If relation == table, then tuple == row. Each tuple maintains a value for each attribute in the relation. The value must be either a member of the attribute's domain or NULL if permitted by the attribute's definition (nullable).

Describe the meaning of a 'table scan'. Why are table-scans something to be avoided when the DBMS executes a query? How are table scans avoided?

A table scan describes the condition where the execution of a query results in the runtime processor scanning (visiting) every record in a table attempting to identify which records meet the SQL statement's selection (filter) criteria. Because every record in a table is being read from disk and into the DBMS processor memory for examination, the table scan is a performance issue causing a query to execute for unacceptably long periods. Table scans are avoided by creating an index on one or more of the table's attributes being selected (filtered) by the query.

What is the name of the SQL mechanism used to qualify (identify) which table a column (attribute) belongs to in a SQL statement? Provide an example in a select statement involving two tables.

Aliases Something using aliases like.... SELECT C.FIRST_NAME, C.LAST_NAME, A.CITY FROM CUSTOMER AS C, ADDRESS AS AWHERE A.ZIPCODE like '75%' AND A.CUSTOMER_ID = C.ID;

Describe how a many-to-many (N-M) relationship between two relations is implemented using relations and foreign keys e.g. Works-On table in Company Schema.

An N-M relationship requires an addition table called the "Join Table". (Also called a Junction Table, or Associative Table in some sources) The Join Table maintains the referencing foreign keys to the primary keys on the two relations being joined. In the Company Schema, the table 'works_on' maintains an N-M relationship between Employee and Project. Also note that the Join Table can maintain its own attributes that describe something about each instance of the relationship e.g. the number of hours each employee works on each project.

Label each of the following statements as describing a Model, Schema, or Application-based constraint. Over the period of a year, an employee cannot move between more than two projects. An employee's salary cannot exceed 200,000.00. Every employee in the enterprise is represented in a single tuple in the Employee relation. The description of an employee is limited to the attributes in the Employee relation. An employee's salary cannot exceed that of the employee's department manager.

Application as this constraint cannot be described in nonprocedural SQL Schema. This constraint can be implemented using a CHECK clause on the insert or update statement. Model based on the use of a primary key to identify each unique employee in the system. Model because the definition of employees is defined by the schema tables and their attributes. Application as this constraint cannot be described in nonprocedural SQL.

How would you describe a full outer join using relation algebra? Just a textual description is fine i.e. you do not need to insert the relational symbols into your answer. Hint: The result set contains three regions.

As the union of a left outer and right outer join; where the result set is divided into three parts: 1) The inner-join between T1 & T2. 2) The remaining elements of T1 (left outer join). 3) The remaining elements of T2 (right outer join).

What feature does the Candidate and Primary key share in common? Which (candidate or primary) type should be used to define foreign keys? What is the difference between the sources of a Natural and Surrogate keys? Which (Natural or Surrogate) is preferred when defining a primary key?

Both candidate and primary keys are a set of one or more attributes whose values should uniquely identify every row in the table. The primary key is used to create foreign key between two tables i.e. the referencing table's FK attribute references the table's primary key. A natural key is an entity's attribute that is found in the problem domain e.g. an employee's social security number or email address. A surrogate key is a table attribute that does not represent an entity attribute but is added to the table to uniquely identify each table row e.g. MySQL's auto-increment column attribute. Generally, a surrogate key should be created for each table that is referenced by a foreign key or otherwise needs to be uniquely identified.

What order are the result set's rows returned in by default? How can we sort by column value the rows that are returned in a SELECT's result-set? Is it necessary that the columns used to order the result-set are included in the result-set? i.e. select <columns> from ...

By default there is no ordering (random ordering) of the result set's rows. We use the SELECT statement's ORDER BY clause. This allows us to specify the ordering of the rows in the result set by specific column's values i.e. ORDER BY C1, C2, ..., Cn. No. The columns used to order the result set do not need to be included in the result set returned by the SELECT.

Briefly describe the difference between the CHAR, VARCHAR and CBLOB data types.

CHAR: This is a character string that uses a fixed amount of storage. The length is assigned when the column is created i.e. CHAR(10). A value that is less than the attribute's length will be padded with spaces. If an insert or update attempts to assign a value that is larger than the given length, the DBMS will reject the operation. VARCHAR: This is a character string that uses a variable amount of storage. It is defined with a maximum size e.g. VARCHAR(200). If an insert or update attempts to assign a value that is larger than the maximum length, the DBMS will reject the operation. Both CHAR and VARCHAR can be filtered in a WHERE clause. CBLOB: Is a Character BLOB (Binary Large OBject). A CBLOB attribute / column can maintain very large amount character data e.g. an XML document of many thousands of bytes. However, the WHERE clause cannot filter on the contents of a CBLOB.

What is the acronym used to describe the four basic operations that can be performed on a database's data? Briefly describe each operation in terms of tables, table rows, and row attributes.

CRUD Create: Create (insert) a new row into a table. Retrieve: Retrieve one or more rows from a database table. Update: Update the attribute values associated with one or more rows of a database table. Delete: Remove (delete) one or more rows from a database table.

This question is written against the Sakila schema. Provide two SQL statements that counts the number of tuples produced by these relational operation: CUSTOMER x ADDRESS FILM × FILM_ACTOR × ACTOR

CUSTOMER x ADDRESS select count(*) from customer, address; This cross product produces 361,197 rows. FILM × FILM_ACTOR × ACTOR select count(*) from film, actor, film_actor; This query will not run to completion on AWS servers because it runs to long (times out).

Identify each of the following as describing either conceptual or internal (physical) views. The ability to provide role-specific views of the database. The ability to add additional attributes to an entity without breaking existing client implementations. The ability to assign objects (schema, tables, etc.) to disk drives without disturbing the schema's design. The view of the database as objects i.e. Files, Buffers, Indexes. The use of Views in the schema.

Conceptual Conceptual Physical Physical Conceptual

Name and briefly describe (1-2 sentences) the First Eight Advantages of Using the DBMS Approach given in the book (not slides).

Controlling Redundancy: Control the problem maintaining the same entity instance twice in the datastore i.e. maintaining the same 'person' as two Customers. Restricting Unauthorized Access: Ensuring that authorized users / clients are provided the correct access to the information maintained by the database. Based on the use of authenticated Client IDs and Roles. Providing Persistent Storage of Application Data: Persisting (making permanent) application entities (objects) CRUD by clients. Providing Search Techniques for Efficient Query Processing: The database maintains data structures, called indexes, that make for the efficient (fast) location of rows matching some query's parameters from tables with very large numbers of rows. Providing Backup and Recovery: The database enforces the safe and correct recovery of the data it maintains from unexpected system failures e.g. crashed disks. The database also provides for the backup (duplication) of its data onto alternative media such as tapes. Providing Multiple Views of Data: The database provides different views of the data each of which is tailored for specific types of clients / users. Not mentioned in this section: the database provides for the safe execution of multiple CRUD operations, from multiple clients, against the shared data. Representing Complex Relations among Entities: The R in RDBMS is relational and a database is capable of representing relationships between entities through the use of identity attributes i.e. Foreign Keys. Enforcing Integrity Constraints: The database will enforce certain properties on the structure of the data it maintains. For example, each table's attribute is defined with a data type (e.g. Integer). If the user / application attempts to set an integer attribute to a non-integer value, the database will reject this operation. This is one of the many integrity constraints provided by the database.

Describe the meaning of the abbreviations DDL and DML. Describe the two types of DML discussed in class. What is a Stored Procedures and where is it executed? Why is a Stored Procedure a more efficient method of updating the database when compared to aggregating data in the client application?

DDL: Data Definition Language is used to create, delete, and modify Schema, Entities (tables), Attributes (Table Columns), Views, and other objects maintained by the database server. DML: Data Manipulation Language is used to CRUD information into / from the database i.e. Insert, Select, Update, and Delete rows from tables. The two types of DML are Nonprocedural and Procedural DML. Nonprocedural DML is the set-oriented SQL we will be discussing throughout the course. Procedural DML also performs set operations on rows in tables. However, procedural DML also provides procedural programming language features such as iteration, branching, local variables etc. Procedural DML allows the developer to integrate complex business rules into the database that would be difficult / impossible to express in a non-procedural fashion. Stored Procedures are procedural DML functions (procedures) that are stored on the DBMS as part of the schema. A SP encapsulates a business operation described by the application requirements. A SP is executed using a request sent by the client. The SP executes entirely on the DBMS as opposed to in the client application. The use of a stored procedure is more efficient because the business logic encoded in the procedure is executed on the database server as opposed to retrieving the result-set records across a network connection and performing the data manipulation in the client application.

Describe the four DBMS user roles described in the slides including the responsibilities assigned to each role.

Database Operators: Operators are responsible for maintaining the servers (machines) that host the DBMS and ensuring the DBMS is operating and available to its clients. Database Administrators: Administrators is responsible for creating and maintaining the database schema. Also approves changes to existing schema requested by the application developers. Application Developers: Responsible for identifying the application entities, attributes, and relationships that need to be captured / persisted in a database schema. Works with the DBA to create the schema that meets the application's needs. Clients: Client applications that CRUD database information. Applications are given roles that restrict their DBMS access to specific databases.

Define each of the following as being an example of an Entity Integrity Constraint or Referential Integrity Constraint. Tuple values must conform to their attribute's domain (type). Foreign Key Constraints. Attributes declared NOT NULL must be assigned a value. Key definitions must uniquely identify every tuple. Referenced entities must not be deleted.

Entity Referential Entity Entity Referential

What are the database-specific definitions of: Entity, Attribute, Relationship, and Metadata? (SPECIAL)

Entity: An object or concept found in the problem domain which needs to be persisted and later accessed. Attribute: A property of a persisted entity identified by a unique name and a data type. Relationship: An association between two entities maintained by the database. Metadata: Data about Data or the data maintained by the DBMS which describes a database's tables attributes, relationships, etc.

This question is written against the Company schema. See Company Schema ER Diagram on eLearning or the hardcopy handed out during class. Translate the following set of relational operations into SQL: RS1 ← EMPLOYEE ⟕ (ssn=essn) DEPENDENT πssn, lname, dependent_name(RS1)

Example of using a left outer join... select ssn, lname, dependent_name from employee left outer join dependent on ssn = essn;

This question is written against the Sakila schema. Translate the following set of relational operations into SQL: RS1 ← CUSTOMER ⨝ (customer.address_id = address.address_id) ADDRESS πfirst_name, last_name, email(RS1)

Example of using an inner join select first_name, last_name, email from customer, address where customer.address_id = address.address_id; or... select first_name, last_name, email from customer join address on customer.address_id = address.address_id;

Given this query against the University schema: "What is the average grade received by all students taking a specific course_number during a specific semester and year?" What are the entities and attributes, and in what order are they accessed to derive a result for this query? Note: Use slide 11 as a template for your answer. Note: Assume that letter grades can be translated into numeric values i.e. A=4, B=3, etc.

From SECTION, identify the Section_identifier value for the given Course_number, Semester, Year. From GRADE_REPORT, collect all the Grade values (rows) that with the Section_identifier. There will be many. Translate letter grades into numeric values and average.

Do your best to create SQL for the following queries. Use Slide 15 as a template. Create a freshman student with the attributes name Yu, CS major, and ID 23. List the grades earned by student with ID 8. Change the course CS3380 to a four credit hour course. Remove MATH2410 as a prerequisite of CS3380.

INSERT INTO STUDENT VALUES ('Yu', 23, 1, 'CS'); SELECT * FROM GRADE_REPORT WHERE STUDENT_NUMBER = 8; UPDATE COURSE SET (CREDIT_HOURS = 4) WHERE COURSE_NUMBER = 'CS3380'; DELETE from PREREQUISITE WHERE COURSE_NUMBER = 'CS3380' AND PREREQUISITE_NUMBER = 'MATH1310';

Match each of the following symbols from Relational Algebra with the corresponding SQL operation. σ (sigma) select π (pi) project ×(times) cross product ⨝ (bow-tie) inner join ρ (rho) rename

In Question

What are the parallels between concepts from Object-Oriented and from Relational Databases?

In both cases, we are maintaining important entities, entity instances, attributes of the entities, and relationships between entities from the application's problem domain. Tables are like OO classes. Tables maintain columns which are similar to class attributes. Instances of classes (objects) are like table rows. Classes maintain associations and tables maintain relationships e.g. the Grade_Report table maintains references to Student and Section.

The basic structure of the CREATE, SELECT, INSERT, UPDATE, and DELETE statements. Understand under what conditions the use of an alias is mandatory. (in slides).

Just know how to do this

What are the "four desired properties of keys" as described in the slides?

Keys should be durable. Once set, the value of a key attribute values should never change. Keys should occupy a single attribute. A natural key made up of 2+ attributes will increase the overhead of both indexing and search (scanning) operations. Keys should be numeric type as to integers can be compared much more efficiently than character (string) types. Surrogate keys are superior to Natural keys.

Describe the effects of each of the following CREATE TABLE column (attribute) constraints. NULL NOT NULL UNIQUE CHECK(expr) DEFAULT(value)

NULL: The column can be unassigned or assigned a NULL value. This is the default. NOT NULL: That only non-null values must be assigned to the column. Attempting to leave a non-null attribute unassigned during an insert or update will result in a DBMS error. UNIQUE: The values assigned to a column must be unique in every row i.e. no duplicate values across multiple rows. CHECK(boolean expr): The boolean expression must evaluate to true for the insert or update to successfully complete. A DBMS error will result if the boolean expression evaluates to false. DEFAULT(value): If a value is not provided for the column in an insert statement, the given default value will be used to assign a value in the new row.

If two customers have 'NULL' addresses, does it mean the customers share the same NULL address? If not, according to the slides what two things might it mean?

No. The NULL address might mean that the customer's addresses is currently unknown or that the customer has no address. The specific meaning depends on the application's requirements.

Describe the effect of these two foreign key constraints? ON DELETE RESTRICT ON DELETE CASCADE Which constraint is default?

ON DELETE RESTRICT: If an attempt is made to delete a record that is being referenced by a foreign key, the delete operation will be rejected. This is the default. ON DELETE CASCADE: If an attempt is made to delete a record that is being referenced by a foreign key, the delete operation will delete both the target row and any rows in referencing foreign key tables.

Describe the three tiers in the three-tier application architecture.

Presentation-Tier: The system components responsible for GUI i.e. presenting information to the users. Service-Tier: The system components responsible for implementing application requirements (business rules) as service interfaces. Data-Tier: The system components responsible for accessing the DBMS.

Identify and describe the three DBMS subsystem that lie between the client's SQL query and the stored database. List in their order of execution.

Query Compiler: Compiles a DML query into a set of primitive data operations against the database tables. Query Optimizer: Optimizes the performance (i.e. attempts to minimize the number of disk accesses) of the data operations against the database's physical structures. For example, determines the indexes to use to optimize the query's execution. Runtime Processor: Executes the optimized query instructions against the physical data stored on the system disks.

Describe the result sets produced by these two select statements. select * from employee, department; and select * from employee, department where employee.dno = department.dnumber; Why is the relationship from department to employee described as 1-N? (SPECIAL)

Select 1 pairs up every employee with every department producing a cross-product of the two tables resulting in N*M rows in the result set.i.e. |employee| * |department|Select 2 also pairs up every employee with every department producing a cross-product of both tables. However, select 2 also provides a where clause that filters out rows from the result set that combine an employee with departments they are not assigned to. Because the foreign key is on Employee.dno referencing Department.dnumber, each employee can provide only a single reference to a department though employee.dno. But many employees can reference a single department.

Which relational operation performs horizontal cuts through a relation's tuples? Which relational operation performs vertical cuts through a relation's tuples?

Select operator Project operator

This question is written against the Sakila schema. Translate the following set of relational operations into SQL: πtitle (σlanguage_id=1(FILM) OR σlanguage_id=3(FILM))

Select title from FILM where language_id = 1 or language_id = 3;

Translate the following relational operations into a single SQL query using a join. Note: You cannot use inner-selects in your answers. RS1 ← σ ssn = essn (EMPLOYEE × DEPENDENT) π ssn, fname, lname, bdate(σ lname = 'Smith'(RS1))

Something like this implicit join: select ssn, fname, lname, bdate from employee, dependent where ssn = essn and lname = 'Smith'; Or something like this explicit join: select ssn, fname, lname, bdate from employee join dependent on ssn = essn where lname = 'Smith';

How does a DBMS client (program) gain access to the services provided by the database server? What information is provided by the client to the DBMS to gain access to the DBMS services? What information is passed between the client and server in the two-phase protocol used by the client to query (select) and retrieve data from the server?

The DBMS is a networked service that is accessed by its clients over a network (TCP) connection / socket. The DBMS clients authenticates themselves to the server using some means of authentication e.g. ID / Password or private key. Once the client has authenticated themselves to the server, the client's session is assigned a client role that determines the client's access to the database's schema objects. The client retrieves information from the database using a two phase request / response protocol. The client submits a SQL statement as a text string (e.g. SELECT statement) to the database server. The server receives and processes the SQL producing a result set. The result set is returned to the client in the response phase of the request / response protocol

Describe result set produced by the SQL 'SELECT * FROM EMPLOYEE, DEPENDENT. Hint: Cartesian Product How do we restrict the pairing of tuples from both sets so that the result set contains only the correct paring of employees with their dependents?

The SQL 'SELECT * FROM EMPLOYEE, DEPENDENT;' produces a result set where every tuple in employee is paired with every tuple in dependent i.e. a cartesian product of the two sets of tuples. We must include a join condition in the select's where clause that restricts which tuples from employees and dependents are paired in the result set. SELECT * FROM EMPLOYEE, DEPENDENT WHERE EMPLOYEE.SSN = DEPENDENT.ESSN;

What assumption is made regarding the database state with respect to model and schema based constraints? What is the assumption regarding database state before and after the execution of one or more SQL operations? Describe the two possible outcomes to the changes made to the database's state during a transaction's execution. How do transactions enforce a correct database state during the execution of multiple statements?

The assumption is that the database is always in a correct state with respect to the relational model constraints and any schema constraints declared in the schema definition. The assumption is that the database state is correct before the execution and will be in a correct state after the execution i.e. the execution moves the database from correct state i to a new correct state j. Commit: The changes made to the database's state during the transaction are made permanent. Rollback: The changes made to the database's state during the transaction are erased. Any change made by a statement in a transaction that causes the database to enter an incorrect state will result in the rollback (abort) of the transaction. When a transaction is rolled-back, every change made to the database state in the transaction will be erased, even the changes made by operations that executed successfully.

Using the University schema... Explain the meaning of foreign key integrity constraint. For example, between COURSE and SECTION as discussed in class and in the text. What is the meaning of 'cascade-delete'?

The entities COURSE and SECTION share a common attribute course_number. The attribute (course_number) uniquely identifies each record / row in the COURSE table. The course_number identifier is used by the entity SECTION to identify which course is being offered for a specific course / semester / year. The constraint is that users are restricted from deleting a course whose course_number is being used by any row in SECTION. If we allowed such a course to be removed, the result would be rows in SECTION referencing non-existent courses. Cascade-delete is a property of a relationship that specifies when an entity (row) is deleted, all of the entities dependent on the deleted entity should also be (automatically) deleted. For example, when a row from COURSE is deleted, the dependent rows from SECTION and PREREQUISITE would also be deleted. Further, because there is a dependency between SECTION and GRADE_REPORT, rows deleted from SECTION will trigger the removal of rows from GRADE_REPORT. So the deletion of a row from a table can trigger a cascade of deletes across the entire schema.

Describe the effects of these two MySQL index declarations. UNIQUE INDEX ID_IDX (CUST_ID ASC) INDEX ZIPCODE_IDX (ZIP_CODE DESC) Which index is suitable for use as a primary key and describe why? Under what circumstances does changing the ordering of an index improve performance of queries against the index? (You may need to research this question).

The first index declares that the values assigned to the CUST_ID column must be unique i.e. two rows cannot share the same value. The first declaration will order the rows in the index in an ascending value order. The second index places no restrictions on the uniqueness of the values assigned to the ZIP_CODE column and this works because there will be many addresses sharing the same zip code. This index will order the rows in the index in a descending value order. The index 'ID_IDX' is suitable for a primary key because it is unique and one of the requirements of any type of key is that it uniquely identifies each row. The ASC / DESC determines the order of the index file entries. By default indexes are arranged ASC. However if a select needs to order its result set in descending order, a DESC index will perform better than the default ASC. See: https://www.mssqltips.com/sqlservertip/1337/building-sql-server-indexes-in-ascending-vs-descending-order/

What is the goal of a "Three Schema Database Architecture"? Describe each of the schemas in this architecture. In which of these schema is a View object most likely to be found? (SPECIAL)

The goals are to: 1) Increase the security of the database by decoupling the client visible presentation of the data from the database's data in its entirety. Security is enforced by defining client roles that limit access to only the information the client needs to fulfill its requirements. 2) Increase the maintainability of the database by decoupling the database structure (tables, indexes, etc.) from the hardware that maintains the data. The three schema levels are: External Schema: An external schema is presents to clients with a specific role. The client's role defines what data from the conceptual schema they can CRUD. Generally, an external schema is defined by creating views of the tables maintained in the Conceptual Schema. Some organizations may create multiple external schema (1 per role). Note: This is not a GUI. Conceptual Schema: The database's schema as defined by the database administrators. The conceptual schema maintains the raw unfiltered tables, attributes, row data, etc. Internal Schema: The mapping of the schema (database) objects to a physical installation. For example, 1) The location of the database on the filesystem. 2) The mapping of tables to files, etc. 3) The allocation of memory and other system resources to the database's installation. Views are most likely found in the external schema where they can be used to provide role-specific filtering, or combine the contents (join) of the entities of the conceptual schema.

What is the meaning / special property of an entity's (table) 'identity attribute'? How are relationships between entities maintained in a Relational DBMS? Use GRADE_REPORT from the University schema as an example. (SPECIAL)

The identity attribute is an entity (table) attribute that uniquely identifies every entity instance. That is, a unique value is assigned to every row of the table which reflects the unique identity of every entity instance stored in the database (every unique Student, Customer, Purchase, etc. has a unique identity attribute value). Relationships are maintained between entities (table) by referencing the identity attribute of one entity by a second entity. The referencing entity maintains an attribute whose values are the identity of the referenced entity. For example in the University schema, the entity GRADE_REPORT maintains two referencing attributes Student_number (STUDENT) and Section_number (SECTION).

What is the default ordering of a relation's tuples when retrieved by a SELECT statement? What is the default order of a tuple's attributes in the result set when retrieved by a SELECT * FROM ... statement? What SQL clause in the select statement is used to specify the ordering of a result set's tuples? How is the ordering of attributes in a result set specified in a select statement?

There is no default ordering of the tuples when retrieved from a relation i.e. the tuples will be returned in an undefined (random) order. The values in a tuple will be returned in the order of the relation's attributes as defined when the relation was created / defined i.e. in the CREATE TABLE statement. The SELECT statement provides an optional 'order by' clause that specifies the order of the returned tuples according to the value of the attributes contained in the result set tuples. The order of attributes in a result set is specified by the order of the attributes in the SELECT ... FROM clause of the select statement.

Briefly describe "Type Compatibility" between two relations. Briefly describe the "except" set operation.

Two relations A and B are Type Compatible if Both relations are of the same degree (# columns). ∀ attribute pair ai, bi; dom(ai) = dom(bi) for 1 ≤ i ≤ n. Type Compatible is also known as Union Compatible. The EXCEPT operation is applied to two sets (like UNION, and INTERSECT) and returns a result set of tuples found in the left-side that are not found in the right-side relation. (Subtraction)

Describe the Two-Tier Client-Server architecture. What are two problem with two-tier architecture identified in the slides? (SPECIAL)

Two-Tier Client-Server architecture describes a fundamental relationship between the DBMS and client application that connect to and CRUD DBMS data. In Two-Tier, clients are desktop (thick) applications that ran on enterprise desktop PCs and network connected directly to the DBMS. This is a pre-internet architecture that has largely been replaced with three tier architectures both in the public internet and in the enterprise. Problems: The DBMS was exposed to the enterprise network making it vulnerable to attack from within the enterprise. Because there was no internet and so no threats from external sources, this vulnerability was considered acceptable. Because thick client applications were installed directly on the enterprise desktop PCs, distributing new application, or updates to existing application, was a support headache.

How do we eliminate duplicate column values from our result set?

We can use the DISTINCT qualifier for the columns we wish to eliminate duplicates.