Question 1: Consider the following information that you need to encode into a table:
CampusID Location
Write the SQL that will
A: Create that table (called Campuses) with the constraint that each element of the campus ID must be UMS followed by two digits.
B. Insert the specific data into the table
Question 2:
Consider a table MaineTowns with columns Town, County, and CongressionalDistrict.
Write a query in SQL that will count the number of towns in each county and congressional district (together) and name the column that will be weirdly named “Number of Towns”. Sort the results by County (descending) and then Congressional District (ascending)
Question 3: (Question about constraints) –you can just list them, don’t have to use in sql- need to convey things that need to be done.
You are making a table for shipping packages for a shipping company. The table has many columns, but for your purposes you only care about height, width, length, and weight. All dimensions are in inches and all weights are in pounds. You do not care how large the package is in dimensions or weight, but as a company, you strictly enforce that a package may not weigh more than ¼ lb. per cubic inch.
Write the appropriate constraints that need to be enforced regarding these four columns. For each type of constraint, say which of the four types (foreign, field, table, front-end) that it is.
Note. There is blatantly obvious one that is listed in the question,but think about this practically. Think about what you know about packages going on a truck. There are some things that must be relevant. For example all physical objects have all of those things ( I can’t make an object with no weight).
Question 4:
You have two tables:
The first table (X) has columns X1 and X2
The second table (Y) has columns X and D
Both X1 and X2 have a foreign key constraint referring to column X in table Y
Write a query to determine all X1, X2, such that X1 and X2 have different values for D.
Note. If you are having trouble with the letters and all of that, this question is motivated by a fairly simple example. Think about the towns in Maine and the towns in New Hampshire. Table X represents neighboring towns, such as Kittery(ME) and Portsmouth(NH) or Kittery(ME) and Eliot(ME). Table Y tells you what state a town is in. For instance, Kittery is in Maine. The query is to find all neighboring towns that are in different states. So Kittery and Portsmouth satisfy that, but Kittery and Eliot do not.
Question 5: (Excel file/table) –Do not need to do this in SQL. ONLY can use what is there, and cannot add an additional field.
You have been provided with a table in the course folder. Create a set of tables that brings that singular table’s information into third normal form. Your answer should include what it means to be that level of normalization, what is wrong with it for each level of normalization (1, 2, and 3) and then the information reorganized into each of those forms.
You need not split the data in your answer, but please identify the columns that will belong to each table and any primary or foreign key that occurs.
NOTE: Many designers will create a field that will make sure that everything is unique without having to do any leg work. You are NOT allowed to do that here. The point of the question is to take a data set and then normalize it.
Question 6
In an object-oriented database, how is a subtype implemented?
- Using a discriminator attribute that identifies the type
- Specifying the subtype only in a drawn schema
- Subtypes are not part of an object oriented paradigm, thus this question is irrelevant
- Defining a table that identifies which classes are related to one another hierarchically
Question 7:
Which of these best describes inheritance?
- Attributes of a subtype art possibly extended by an additional table (the supertype). Thus the supertype inherits data from the subtype
- An object such as a car inherits information about each of its parts. If the parts are separated, the objects ceases to be a car.
- A link table is created to relate objects to one another, thus the link table inherits information from both object tables
- Attributes of a supertype are possible extended by an additional table (the subtype). Thus the subtype inherits data from the supertype
Question 8:
Which of these would be an example of inheritance?
- A car and its components parts with additional qualities pertinent to those types of component parts
- Vehicles with classifications based on the type of vehicle and additional qualities pertinent to those types of vehicles
- Highways with classifications based on road jurisdiction (state, federal, local) with additional qualities pertinent to those types of road
- All of these are examples of inheritance
- Exactly two of these are examples of inheritance
Question 9:
Which of these is an example of an association?
- Mammals and canines
- A car and its component parts
- A folder and its component files
- University employees and faculty
Question 10:
What distinguishes aggregation from association?
- A supertype is an example of an aggregation of its subtypes, while a subtype is associated with a supertype
- Aggregations are easily modeled by a relational databases while associations are not
- Aggregations link classes together, while associations create entirely new units that are dependent upon the contributing classes (for example a team plays games, versus a team is made up of players)
- Associations link classes together, while aggregations create entirely new units that are depended upon the contributing classes (for example, a team plays games, versus a team is made up of players)
Question 11:
Match the form of normalization with the anomaly problem that it removed (note: to keep the integrity of the answers, I have omitted any level designations within the requirements – such as 2NF requires 1NF)
Question 12:
Which of these is not a type of anomaly addressed by the normalization process?
- Update anomalies
- Insertion anomalies
- Contradiction anomalies
- Deletion anomalies
Question 13:
Which of these is an example of the relational concept jointly exhaustive, pairwise disjoint?
- All cars produced by manufactures have a specific make ( such as Ford, Chevrolet, Subaru, etc) and no car can have more than one make. RELATION: Car to Make
- Any professional athlete belongs to at most one team in any particular sport, but could play multiple sports (e.g., Brian Jordan played for the Atlanta Falcons in the NFL and the Atlanta Braves in the MLB. A professional athlete is anyone who belongs to a team or has not officially retired from their sport. RELATION: Athlete to Team
- All beverages can be classified as milk, hot, carbonated, juice, or tea, even including an Arnold Palmer which is a combination of a juice (lemonade) and a tea (sweet tea) and would be tabulated as both juice and tea. RELATION: Beverage to Beverage Type
- Most employees are either salaried, hourly, or contracted, but there are other employees that are different types than these that are irrelevant for your system. RELATION: Employee to Employee Type
Question 14:
Which of these would not be involved in the definition of a 5-digit ZIP Code field if you are trying to constraint it, provided that the table that has the column requires the ZIP Code to be there?
Note: Think about my office ZIP code: 04330 as you answer this question…
- CHAR(5) or VARCHAR(5)
- LIKE “00000”
- NOT NULL
- INT(5)
Question 15: — YOU can choose multiple boxes on this
Assume that your table has two fields that could serve as primary keys and you wish to constrain them appropriately such that each of them remains as if they were a primary key. Let’s call these fields Artist and Painting. Assume that painting is declared the Primary Key. What keywords(s) must be used with Artist to make this constraint possible?
- AUTO-INCREMENT
- UNIQUE
- PRIMARY KEY
- NOT NULL
Question 16: – Multiple possible choices available
Which information is needed to write a foreign key constraint in SQL?
- The column in the table that is being used to check
- The data type of the fields being checked
- The table that is used to check
- The field in the table being constrained
Question 17:
How does an SQL command end?
- WHERE and a constraint
- ;
- )
- END
Question 18:
Lets say you are writing an SQL query, in which clause will you find the tables that are being used in the query?
- WHERE
- GROUP BY
- FROM
- SELECT
Question 19:
How is the order of columns overridden for the output of a query?
- Based on the order they were stored in the tables in the order the tables occur in the Answer to Q18 (above)
- Based on the ORDER BY clause
- The order in which they appear in the SELECT clause
- The order in which they appeared when the table was created using CREATE TABLE
Question 20:
Which keyword is used to alias (rename a table or column?)
- This cannot be done
- ALTER
- AS
- CONSTRAINT
Question 21:
Match the join type with its definition
Question 22:
Why would we denormalize?
- It can speed up queries
- We have no reason to functionally edit data (or the need for that is minimized)
- It avoids complex joins in the queries
- All of the above
Question 23: – We weren’t showed this or talked about, so it probably doesn’t apply
How is a front-end solution handled in SQL?
- SQL has a specific front end language that gets used for database purposes
- Using the keyword FRONT END
- It isn’t. So long as the data type is correct the input is accepted
