Database Management Systems Exam 1 – Important Questions & Answers.

Database Management Systems Exam 1 – Important Questions & Answers. What are the 5 SQL built in functions? COUNT SUM AVG MIN MAX Entity some identifieable thing that users want to track. Relation a two-dimensional table that has certain characteristics Characteristics of a relation -each relation in a database has a unique name -rows contain data about a specific instance of an entity -columns contain data about attributes of the entity class -all entries in a column are of the same kind -each column in a given table has a unique name -cells of the table hold a single value -the order of the columns is unimportant -the order of the rows is unimportant -no two rows may be identical What are two words that can be used interchangably with "Relation" Table File What are two words that can be used interchangably with "Attribute" Column Field What are two words that can be used interchangably with "Tuple" Row Record Functional Dependency occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s) if A (B,C) then A B and A C if (A,B) C then neither A nor B determines C by itself What makes a determinant value unique? a determinant is unique in a relation if and only if, it determines every other column in the relation. You cannot find the determinant of all functional dependencies simply by looking for unique values in one column Key a combination of one or more columns that is used to identify rows in a relation. Composite key a key that consists of two or more columns Candidate key a key that determines all of the other columns in a relation Primary key a candidate key selected as the primary means of identifying rows in a relation. There is ony one primary key per relation. The primary key may may be a composite key the ideal pimary key is short, numeric, and never changes. Surrogate key An artificial column added to a relation to serve as a primary key. Foreign key the primary key of one relation that is placed in another relation to form a link between the relations. can be a single column or a compostie key. the term refers to the fact that key values are foreign to the relation in which they appear as foreign key values. referenctial integrity constraint a statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation. Anomaly something unusual, unexpected or abnormal. bad things when we work with databases. Modification Anomalies Deletion anomaly insertion anomaly update anomaly Normal form a relation is in normal form if it conforms to a defined set of rules (norms) a relation that conforms to the x set of rules is said to be in xNF. The purpose is to help us prevent anomalies. 1NF a table that qualifies as a relation 2NF all of its nonkey attributes are dependent on all the attributes of the primary key 3NF if it is in 2NF and has no determininants except the primary key Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. Process for putting relations into BCNF 1. identify every functional dependency ify every candidate key there is a functional dependency that has a determinant that is not a candidate key the columns of that functional dependency into a new relation the determinant of that functional dependency the primary key of the new relation a copy of the determinant oas a foreign key in the original relation e a referential integrity constraint between the origninal relation and the new relation. 4. repeat step 3 until every determinant of every relation is a candidate key Multivalued dependency occurs when a determinant is matched with a particular set of values. The determinant of this dependency can never be a primary key. Advantages of Normalization Eliminates modification anomalies. Reduces duplicated data. Disadvantages of Normalization More complicated SQL required for multitable subqueries and joins. Extra work for DBMS can mean slower applications. Denormalization the joining of the data in normalized tables prior to storing the data. The data is then stored in nonnormalized tables. Inconsistent values occur when different users, or different data sources, use slightly different forms of the same data value. What is another name for a "set of tuples" a relation or a table A is an ordered list of related attributes tuples SELECT condition in relational algebra a subset of the tuples from a relational expression that satisfies a selction condition. In a relational database design, all relationships are expressed by Creating a foreign key. Which database system is not an enterprise-class? Microsoft Access. When representing a 1:1 relationship in a relational database design... The key of either table may be places as a foreign key into the other To represent a one-to-many relationship in a relational database design... The key of the parent is placed as a foreign key into the child. In many-to-many relationships in a relational database design... They keys of both tables are placed in a third table When transforming an E-R data model into a relational database design, they key of the parent entity should be placed as part of the primary key into the child entity ... when the child entity is ID-dependent For the M-O (parent mandatory, child optional) case, what Define referential integrity constraint from child to parent and make foreign key NOT actions(s) should be taken to ensure minimum cardinality NULL. is maintained? Cascading updates a referential integrity constraint policy that insures that foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table. Cascading deletes a referential integrity constraint policy that insures that all rows containing a particular foreign key value in a table are eliminated from the table when the row containing the correspoinding primary key value in a parent table is eliminated from the database. Which of the following is not a key element of an E-R model? A)relationships B)entities C)objects D)attributes C) Objects Entity instance an occurrence of a particular entity Attributes The way a thing is described in terms of its characteristics An identifier may be ... composite For a relationship to be considered a binary relationship what must happen ? it must involve exactly two entity classes. Which of the following is NOT a common variant of the E-R model? A)IE Crow's Foot B)IDEFIX C)UML D)Object-oriented B) IDEFIX Maximum cardinality the most instances of one entity class that can be involved in a relationship instance with another entity class. Minimum cardinality Whether or not an instance of one entity class is required to be related to an instance of another entity class. When is a database considered self-describing? When it contains a description of its own structure Weak entity an entity whose identifier includes the identifier of another entity Weak entity An entity whose existence depends on the presence of another entity but whose identifier does not include the identifier of the other entity A subtype entity is considered what type of entity? a weak entity TF : All subtypes of a supertype must have the same attributes False Identifier An attribute that determines which subtype should be used What type of relationship to supertype/subtype entities have ? IS-A How do you represent a multivalued attribute in an E-R model ? Create a new ID- dependent entity with a 1:N relationship. TF: Primary keys must contain numeric data False TF: Primary keys can be used to create relationships between tables True TF: Primary keys uniquely identify a row in a table True TF: Primary keys can be part of relational integrity constraints True recursive relationship when an entity has a relationship to itself What is the first step when you are given a set of tables and asked to create a database to store their data? access the existing tables' structure and content Anomalies caused by functional dependencies can be eliminated by using what NF? BCNF a table is said to be in BCNF if ... all determinants are candidate keys. What is a missing value called ? a null value TF: relations have rows containing data about an entity True TF: relations have columns containing data about attributes of the entity True TF: relations have cells that hold only a single value True TF: relations can have two identical rows False Saying that two entities are functionally dependent means that ... for one of the entities, if we are given the value of that entity, we can determine the value of one other entity. Composite key a combination of two or more columns used to identify particular rows in a relation. Candidate key a determinant that determines all the other columns in a relation. surrogate key an artificial column added to a relation to serve as the primary key referential integrity constraint used to limit the possible values of a foreign key having to enter facts about two entities when we want to enter facts about only one is an example of what kind of anomaly? insertion anomaly. A table that meets the definition of a relation is said to be in what form ? First normal form A relation is in third normal form if and only if it is in 2NF and there are no non-key attributes determined by another non-key attribute. How many themes should each relation have ? one and only one What SQL keyword must be used to remove duplicate rows from the result table? DISTINCT What SQL keyword is used to sort the result table by the values in one or more columns? ORDER BY What is the purpose of a database? The purpose of a database is to help people track things of interest to them. What is the most commonly used type of database? Define the terms data and information. Explain how the two terms differ. Data = recorded facts and figures Information = knowledge derived from data Databases record data, but they do so in such a way that we can produce information from the data. Give examples of a single-user database application and a multiuser database applica- tion other than the ones shown in Figure 1-5. single user: a relator might want to use a database to keep track of their houses, clients, houses sold, prices, and so on. multiuser: Bookstore to keep track of books. Give an example of a database application that has hundreds of users and a very large and complicated database. Oracle Database from Oracle Corporation What is Structured Query Language (SQL), and why is it important? is an internationally recognized standard database language that is used by all commercial DBMSs What does DBMS stand for? Database Management System Name three vendors of DBMS products. Microsoft Access 2013 MySQL 5.6 IBM DB2 Oracle Corporation Oracle Database 11g Release 2 What is metadata? How does this term pertain to a database? The self-describing data is called metadata, which is data about data Is Microsoft Access a DBMS? Why or why not? Yes What is the name of the DBMS engine within Microsoft Access? Why do we rarely hear about that engine? Access Data Engine (ADE). Who was E. F. Codd? The dominant database model is the relational database model—all current major DBMS products are based on it. It was created by IBM engineer E. F. Codd in 1970. What is the NoSQL movement? Name two applications that rely on NoSQL databases. The NoSQL movement is really a NoRelationalDB movement that replaces relational databases with nonrelational data structures. The NoSQL approach, which is used by Facebook and Twitter, often is based on XML. What is an ad-hoc query? What does SQL stand for, and what is SQL? Why is SQL described as a data sublanguage? What does DML stand for? What are DML statements? What does DDL stand for? What are DDL statements? What is the SQL SELECT/FROM/WHERE framework?