Data Management Applications C170 with complete solution. Graded A+. 2022/2023.

Document Content and Description Below

Data Management Applications C170 with complete solution What is a table? Answer- A table is a persistent representation of a logical relation that is a relation whose contents can be saved for per... manent use. What are the characteristics of a relational table? Answer- The characteristics of a relational table are that a table is perceived as a two-dimensional structure composed of rows and columns, each row (tuple) represents a single entity occurrence within the set, each column represents an attribute, each column has a distinct name, each intersection of a row and column represents a single data value, all values in a column must be of the same data format, the order of the rows and columns don't matter to the DBMS, and each table must have an attribute (or combination of attributes) that uniquely identifies each row (tuple). What is a tuple? Answer- A tuple is a row that represents a single entity occurrence within a set. What are records? Answer- A record is the same as a tuple (row). What is a field? Answer- A field is a column. What is a key? Answer- A key consists of one or more attributes that determine other attributes. For example, a social security number identifies all of the other attributes related to that person. What is determination? Answer- Determination is the state in which knowing the value of one attribute allows you to determine the value of another attribute. For example, if you know a key, you can find other information. What is functional dependence? Answer- Functional dependence is when knowing the value of one or more attributes lets you know the value of the other attributes. For example, knowing the first name and the social security number would let you find the person's last name. What is full functional dependence? Answer- Full functional dependence is when knowing the value of one or more attributes lets you know the value of other attributes and the value of the known attributesare NEEDED to find the other attributes. For example, a social security number lets you find the first and last name of someone. This is full functional dependence. If it was a social security number and a first name this would just be functional dependence because the first name is not required to look up the last name. All you need is the SSN. What is a determinant? Answer- A determinant is an attribute whose value determines the value of other attributes. For example, a social security number could be the determinant for your last name. What is a dependent? Answer- A dependent is an attribute whose value is determined by another attribute. For example, you last name could be a dependent of your social security number. What is a composite key? Answer- A composite key is a key that is composed of one or more attribute. For example, the key of your last name and you social security number could be used to determine your first name. This would be a composite key because you are using your last name and your SSN in the key. What is a superkey? Answer- A superkey is a key that can uniquely identify any row in a table. Some examples of this would be your social security number identifying a row corresponding to your information, but a superkey isn't limited to full functional dependence. Your SSN and your last name used as a key would still be a superkey. What is a candidate key? Answer- A candidate key is a minimal super key. This is to say it has no extra attributes. If you only need a social security number to identify the row related to a person than the candidate key is the SSN. A key consisting of an SSN and the last name in this case would not be a candidate key. What is entity integrity? Answer- Entity integrity is the condition in which each row (entity instance or tuple) in the table has it's own unique identity. This requires that all values in the primary key be unique and that the primary key cannot be null. What is a null? Answer- A null is the absence of a data value. A null should be avoided whenever possible. In fact, you can set a rule that prevents an attribute from being null. A null can create an issue when a function like average is used.What is a foreign key? Answer- A foreign key is the primary key of one table that has been placed into another table to create a common attribute. For instance, a student id number could be the primary key for information on a student but a foreign key for a table containing a class roster. What is referential integrity? Answer- Referential integrity is the condition in which every reference to an entity instance by another entity is valid. In other words, a foreign key needs to be either null or a valid primary key in another table. What is a primary key? Answer- A primary key is a candidate key selected to uniquely identify all other attributes in a given row and it may not contain null entries. Examples of this include student ID's and social security numbers. What is a secondary key? Answer- A secondary key is a key that is used strictly for data retrieval purposes. A secondary key does not always return a unique outcome. For instance, if a person doesn't know their student ID, you could still look them up by their first and last name in the system but that might return several students with the same name and you'd have to look through the list to find the appropriate one. What is a flag? Answer- A flag is used to indicate the absence of some value to avoid using a null. What is relational algebra? Answer- Relational algebra defines the theoretical way of manipulating table contents using relational operators. What is a relvar? Answer- A relvar is the variable that holds a relation. It is composed of two parts: a relvar heading and a relvar body. What is a relvar heading? Answer- The relvar heading contains the name of the attributes. What is a relvar body? Answer- A relvar body contains the relation. What is a relation (not in general terms where you can use table and relation interchangeably)? AnswerA relation is the data that we see in our tables not the data itself. It's a relationship between sets of information.What is the property of closure? Answer- Closure is a property that permits the use of relational operators on existing relations to create new relations. What is a unary operator? Answer- A unary operator in regards to SQL is an operator that works on only one table. What is the SELECT (sometimes referred to as RESTRICT) operator used for? Answer- The SELECT (or RESTRICT) operator is used to select certain rows (aka tuples). It yields values for all rows that satisfy a given condition. For example a SELECT statement that looks for students with a gpa below 2.0 could be used to produce a table with all rows that contain a gpa lower than 2.0. Formally, SELECT is denoted by a lower case sigma symbol. This is a unary operator that is it only accepts one table as input. What is the PROJECT operator used for? Answer- The PROJECT operator is a unary operator that will return only the attributes requested that is to say it yields columns. It does not limit the rows returned so it will return all rows within the requested columns. Formally it is denoted by the Greek letter pi. What is the UNION operator? Answer- The UNION operator combines all rows from two tables excluding duplicate rows. To be used in the UNION the two tables must contain the same attributes. Union is denoted by a symbol that looks like an uppercase U. What does it mean to be union-compatible? Answer- Union-compatible means that two or more tables contain the same number of columns of the same type. What is an INTERSECTION operator? Answer- An INTERSECTION operator is an operator that yields only rows that are in both tables. This operator requires that the tables be union-compatible. The symbol for this operation is similar to an upside down U. What is the DIFFERENCE operator? Answer- The DIFFERENCE operator is an operator that yields all the rows in one table that are not found in another table. It is seen as subtraction and it denoted by the minus sign -. It's important to note that given tables A and B the DIFFERENCE between A and B is not the same as the DIFFERENCE between B and A. Oder matters. What is the PRODUCT operator? Answer- The PRODUCT operator is an operator the yields the Cartesian product of two tables that is it yields all possible pairs of rows of the two tables. So, if one table has 3rows and another table has 6 rows the PRODUCT of these two tables will result in a table containing 3 x 6 rows which equals 18 rows. It is represented by the multiplication symbol. What is the JOIN operator? Answer- The JOIN operator allows information from two tables to be intelligently combined into one table. It is the real power behind relational databases and allows for the use of independent tables linked by common attributes. What is a natural join? Answer- A natural join links tables by selecting only rows with common values in their common attributes. It is the result of a three stage process. First, it takes the PRODUCT of two tables thereby yielding all possible pairs of rows. Then it takes the SELECT that produces a table that contains rows such that the value of a certain attribute that are equal. Finally, it performs the PROJECT operator to ensure that there are no duplicate columns. An example of this would be if you had two tables contain student ID numbers that you wanted joined. It would first perform a Product producing a large table with all common rows. Then it would SELECT the rows in which the value of the student ids that occur in the same row are equal. Then it uses the PROJECT to eliminate the duplicate columns which in this case at the very least would get rid of one of the student id columns. What is an equijoin? Answer- Equijoin is a join operation that is performed in two steps. The first step takes the PRODUCT of two tables yielding all possible pairs of rows. Then it SELECTS rows that such that the values of a joined attribute are equal. In fact, this result is the second step of the natural join operation. This means that duplicate columns are not removed so you see the two values that are equal in the same row. What is a thetajoin? Answer- A thetajoin is any join operator that uses an ineqality comparison for the join attribute. So, it would make use of less than, greater than, less than or equal to, and greater than or equal to operators in the join statement. It works using the same two steps as the equijoin. What is an inner join? Answer- An inner join produces a table that has matched records. Natural joins and equijoins are good examples of this. What is an outer join? Answer- An outer join is a join where the matched pairs would be retained, and any unmatched values in the other table would be left null.What is a left outer join? Answer- This is an outer join where the first table of the join keeps the unmatched values and the second table has the values that are set to null. What is a right outer join? Answer- This is an outer join where the second table of the join keeps the unmatched values and the second table has the values set to null. What are outer joins particularly useful for? Answer- Outer joins are a great way to ensure referential integrity. This is especially true when adding tables that aren't from a database into a database. It's a good way to check for foreign key matches. What is the DIVIDE operator? Answer- The DIVIDE operator is an operator that is used to answer questions about one set of data being associated with all values of data in another set of data. For example, if you have a table that shows a list of product numbers associated with customer numbers indicating which customers purchased which products, and you have a second table which contains a list of products offered and you want to find out which customers purchased every product, you would DIVIDE the first table by the second table. What is a data dictionary? Answer- A data dictionary provides a detailed description of all tables in the database created by the user and the designer. It contains a minimum of all the attribute names and characteristics for each table of the system. Basically it contains metadata that is data about data. It doesn't contain the data itself, but it does contain the names and characteristics of said data. What is the system catalog? Answer- The system catalog can be described as a detailed system data dictionary that describes all objects within the database, including data about table names, table's creator and creation date, number of columns in each table, data type corresponding to each column, index filenames, index creators, authorized users, and access privileges. What are homonyms in the context of database design? Answer- Homonyms are similar sounding words with different meanings. In the case of databases, they can be seen as attribute names that are the same across tables but represent different data. For example, using S_ID might seem like a good idea for a label for the student ID attribute in a student table until you realize you also used S_ID as the label forthe school ID attribute that identifies different schools in the state. You want to avoid this as much as possible and the system catalog can help you achieve this goal. What are synonyms in the context of database design? Answer- Synonyms are words that sound differently but represent the same thing. In the context of database design, this could be something like using STU_ID for student id in one table and then using S_ID for the student id attribute name of another table. You want to avoid this as much as possible. What is the 1:M relationship? Answer- This is the one to many relationship. It is the ideal situation for database design. A good example to illustrate this is the example of a COURSE table in a college. A single course might generate many classes, but each CLASS can only be related to one COURSE. What is the 1:1 relationship? Answer- This is the one to one relationship. For example, one DEPARTMENT can only have one chair which is a PROFESSOR and one PROFESSOR can only chair one DEPARTMENT. What is the M:N relationship? Answer- This is a many to many relationship. It is not allowed in relational database design. In fact, if this relationship exists, you will need to turn it into a 1:M relationship through the use of another entity that has a 1:M relationship with the entities that have the M:N relationship. What is a composite entity? Answer- A composite entity (also known as a bridge entity or an associate entity) is an entity that is used to link tables that were originally in a M:N relationship. It is required for this table to at least contain the foreign keys from both tables that were in a M:N relationship. When defining a composite table's primary key, a database designer can either create a new primary key or he can create a composite key containing the foreign keys of the other two tables. What is modality? Answer- Modality is the minimum number of times an instance of an entity can be associated with an instance in a related entity. What is cardinality? Answer- Cardinality is the maximum number of times an instance of an entity can be associated with an instance in a related entity. What is an index? Answer- An index is an orderly arrangement used to logically access rows in a table. This makes retrieval much faster. An index is composed of an index key and several pointers.What is an index key? Answer- An index key is the thing that lets you quickly find information. For example, if you index information on paintings and you want to find information about the paintings of a specific painter, you could look up the index key of the painter and follow the pointers listed in the index to quickly access data on those paintings. What is a unique index? Answer- A unique index is an index where each index key can only have one pointer associated with it. The CUSTOMER table's primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. Why type of integrity is this? Answer- Entity integrity. What is the entity relationship diagram? Answer- The entity relationship diagram represents the conceptual database as viewed by the end user. It depicts a database's main components: entities, attributes, and relationships. In the ERM (entity relationship model) what does an entity represent? Answer- In ERM, an entity represents and entity set which corresponds to a table not a row. What is a table row referred to as in the entity relationship model? Answer- A table row is referred to as an entity occurrence or an entity instance in an entity relationship model. In the crow's foot model how are entities represented? Answer- Entities are represented by a rectangle in the crow's foot model. At the top of the rectangle there is a table name in a smaller inner rectangle and then below that rectangle are the attribute names listed in another rectangle. What is a required attribute? Answer- A required attribute is an attribute that must have a value. These attributes are represented in boldface in crow's foot notation. What is an optional attribute? Answer- An optional attribute is an attribute that doesn't have to have a value and can therefore be left empty.What are identifiers? Answer- Identifiers (primary keys) are one or more attributes that uniquely identify each instance. Identifiers are underlines in the entity relationship diagram, and in crow's foot notation there is usually a PK next to it indicating that it is the primary key. What is a composite identifier? Answer- A composite identifier is an identifier made up of more than one attribute. What is a composite attribute? Answer- A composite attribute is an attribute that can be further divided into more meaningful attributes. For instance, a name can be divided into a first and a last name at the very least for most people and maybe even more. What is a simple attribute? Answer- A simple attribute is something that can't be subdivided into meaningful parts such as someone's age. What are single valued attributes? Answer- Single valued attributes are attributes that may only contain a single value for instance a person can only have one social security number. What are multivalued attributes? Answer- Multivalued attributes are a attributes that can contain more than one value. Crow's foot notation does not identify multivalued attributes in any special way. It is worth noting that multivalued attributes should NOT be implemented as such in the RDBMS. How should a multivalued attribute be implemented in the RDBMS? Answer- A multivalued attribute can be implemented one of two ways. The first way involves creating several new attributes within the original entity one for each component of the original multivalued entity. This is only acceptable if each instance in the table will have values for each of these new attributes. The second option is the create an entire new entity with the name of the multivalued attribute. This creates a much more flexible expandable solution that doesn't compromise the original table integrity through the use of a 1:M relationship. What is a derived attribute? Answer- A derived attribute is an attribute that is derived from other attributes that is to say a value such as an average for your grades would be an attribute derived from your individual grade attributes and a formula. A derived attribute does not have to be stored in the database. It can also be calculated when it is needed depending on how you choose to design your system. This is sometimes referred to as a computed attribute.What are participants? Answer- Participants are entities that participate in a relationship. The relationship name will always be an active or passive verb. For example, a STUDENT takes a CLASS. The relationship is takes and the two participants are STUDENT and CLASS. This always works in both directions. For instance, a STUDENT takes a CLASS and a CLASS is taken by a STUDENT. What is connectivity? Answer- Connectivity is the classification of the relationship between entities. For example, 1:1, 1:M, and M:N. What is existence-dependent? Answer- An entity is said to be existence-dependent when it cannot exist on its own. In more plain terms, an entity is existence-dependent if it is required to have a foreign key. What is existence-independent? Answer- Existence-independent entities can exist on their own. This is to say, they don't need foreign keys or other entities to exist. This can also be referred to as a strong entity or a regular entity. For example, if you look at the two entities PARTS and VENDOR. The VENDOR supplies PARTS could be a true statement regarding their relationship, but if VENDOR doesn't supply all the parts then PARTS is existence-independent. What is a weak relationship? Answer- A weak relationship, also known as a non-identifying relationship, if the primary key of the related entity does not contain a primary key component of the parent entity. Crow's foot notation depicts a weak relationship between entities by placing a dashed line between those entities. What is a strong (identifying) relationship? Answer- A strong (identifying) relationship is a relationship where the primary key of the parent entity makes up part of the primary key of a related entity. For example, COURSE has the primary key CRS_CODE and CLASS has the primary key, CRS_CODE and CL_SECTION. Note that this primary key is a composite key. Crow's foot depicts a strong relationship with a solid line between entities. What is a weak entity? Answer- A weak entity is one that is existence-dependent and has the primary key partially or totally derived from the parent entity (aka strong relationship).What is optional participation? Answer- Optional participation means that a relationship between two entities isn't required to take place. For example, CLASS generates COURSE, but it doesn't have to. Some semesters a class won't be offered. What is mandatory participation? Answer- Mandatory participation means that one entity occurrence requires another entity occurrence. If no symbol exits to show it is optional then it is assumed to be mandatory by default in entity relationship modeling. What is relationship degree? Answer- The relationship degree indicates the number of entities or participants associated with a relationship. What is a unary relationship? Answer- A unary (aka recursive) relationship is when an association exists with a single entity. For example, an EMPLOYEE manages an EMPLOYEE. What is a binary relationship? Answer- A binary relationship exists when two entities are associated. For example, a PROFESSOR teaches a CLASS. What is a ternary relationship? Answer- A ternary relationship exists when three entities are associated. For example, a TEACHER grades a EXAM and a TEACHER grades a STUDENT. In practice this relationship would look something like TEACHER grades EXAM, STUDENT receives EXAM, and EXAM appears in REPORT CARD. What is a recursive relationship? Answer- A recursive relationship is one in which a relationship can exist between entities of the same set. For example, EMPLOYEE manages, marries, or is related to EMPLOYEE. All three of those relationships would be recursive. What is SQL. Answer- SQL is both a data definition language and a data manipulation language. What is a data definition language? Answer- A data definition language is a standard for commands that define structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users. They can also be used to define data access rights.What is a data manipulation language? Answer- A data manipulation language is a family of syntax elements used for updating, deleting, or inserting data in a database. What does the command CREATE SCHEMA AUTHORIZATION do? Answer- CREATE SCHEMA AUTHORIZATION is a SQL DDL command that creates a database schema. What does the command CREATE TABLE do? Answer- CREATE TABLE is a SQL DDL command that creates a new table in a user's database schema. What does the command NOT NULL do? Answer- NOT NULL is a SQL DDL command that ensures that a column (attribute) can't have a NULL value. What does the command UNIQUE do? Answer- UNIQUE is a SQL DDL command that ensures that a column (attribute) value is not duplicated anywhere else in that column. For example, this could be useful for student ID, SSN, etc. columns. What does the command PRIMARY KEY do? Answer- PRIMARY KEY is a SQL DDL command that defines the primary key for a table. What does the command FOREIGN KEY do? Answer- FOREIGN KEY is a SQL DDL command that defines the foreign keys of a table. What does the command DEFAULT do? Answer- DEFAULT is a SQL DDL command that defines a default value for a column (attribute). This prevents NULL's from occurring as it automatically assigns a value unless otherwise stated. What does the command CHECK do? Answer- CHECK is a SQL DDL command that validates the data in an attribute. For example, when someone is entering data into a table it could check to see if that data is less than or equal to 21. What does the command CREATE INDEX do? Answer- CREATE INDEX is a S [Show More]

Last updated: 1 year ago

Preview 1 out of 30 pages