Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ISYE 2530. Information and Data Systems.

Final Exam, Fall 2023

Instructions:

This assignment consists of two parts, both working with the ‘Rich and Famous’ database we have used for the midterm. The first part is a couple of standalone questions, the second part is a mini-project.

Once you are finished, you can uninstall MySQL if you think you won’t need it again soon.

Standalone:

Using the current version of the database (one with Octavian in it, it should be in your MySQL) provide the following queries and their results:

1)  Make a query which will produce the distribution of occupations (a table with two columns: occupation and number of occurrences) (2 points).

2) EXTRA CREDIT, 2 points. Can you think of a query that will do the same, but produce the fraction of each occupation occurrences (number of occurrences/total occurrences), WITHOUT using the knowledge that there are 7 tuples? (Hint: a subquery can be used in an arithmetic operation!)

3) Count the Romans (a table with a single column and row containing the number) (2 points)

4) Also count the non-Romans (1 point)

Part 2: Mini-Project.

You may have already noticed that the ‘Rich and Famous’ database makes little sense in terms of its contents. This is because it was created to be a bad example (learning is better on bad examples  ). Now, let us give it a purpose and try to redesign in according to that.

Background:

This data will be intended as a part of a Wiki-Project, containing summary information on people, food, and fictional characters (among other things). We also would like to be able to link them together in various ways (by name, occupation, other things in common).

We want to divide our entities into 3 types: people, food, and fictional characters, with each type having its own attribute set:

People have First Name, Last Name (all Romans will have the last name Caesar, Francis is Drake, Elon is Musk), Occupation (and let’s get rid of ‘web’ and birthdate for now).

Food will have an attribute ‘type’ (soup, salad, etc)

Fictional characters will have an attribute ‘genre’ (cartoon, movie, comicbook, etc.)

All three categories need to have an attribute indicating to which category an entity belongs (person, food, fictional).

Assuming there may be other entities in the database (not just those that are listed now), answer the following questions.

5) Is anything missing from this initial setup (hint, as we got rid of ‘web’)? (2 points)

6) What would be the best way to correct it? (3 points)

Now, we will not be dealing with achievements, and the Occupations need to be modified as follows: we remove Incomes completely, and instead of it add ‘Employer’. For anyone Roman the employer will be Rome (and we’ll remove the word ‘Roman’ from the occupation name), for Francis Drake it will be England, for Elon Musk it will be Twitter (that’s where he’s been the busiest lately  ).

7) Can we still use the old key for the relevant table (occupation name) in this situation? (2 points)

8) What is the new key? (3 points)

For the subsequent questions there may be different solution choices. I will be grading based on your logic, not on my intended solution.

9) The high level approach to Information Systems design is an E/R diagram. Based on the structure outlined above, create one for this intended system (5 points)

(Hint, it will probably require subclasses, although there might be a way to go without them)

One factor affecting design choices that I haven’t mentioned in class is whether we are integrating with an existing system. In this case we may have no choice of the database model (for example), as we are forced to use the model of an existing system.

In our case let us assume that we are integrating it with a pre-existing Wiki, which is a relational database.

10) Convert your E/R model into a Relational Database. Provide only the schemas as the answer (5 points). If utilized subclasses, use Object-Oriented approach!

Now let’s try to make an interesting query using your new schema. You may want to create the new database in MySQL (can make tables as CSVs and import, or use the commands), or create CSVs and load them into R to use sqldf.

Additional data mentioned above. People are Julius Caesar, Octavian Caesar, Nero Caesar, Francis Drake and Elon Musk.

Caesars work for Rome, Drake works for England, Musk works for Twitter.

11)  Let us create a ‘disambiguation page’ for all the Caesars. Make a query that will output the identifying information (whatever you came up with earlier) and the category for anything with ‘Caesar’ in the name. Assume that there may be a fictional Caesar as well (5 points).

Hint: this may not be as straightforward as it seems, and you will need a rather complicated condition, and to pull things from multiple tables at once. To remind, attributes with the same name in different tables can be referred as table.attribute.

12) (Up to 3 extra points, may be more in exceptional cases) Can you think of a way to also add some other information to the output of the previous question? For example, if we wanted to also output occupations for people, kinds for food, and genres for fictional characters?

Note: I didn’t have an answer myself at the time the assignment was created