Study the schema and sample data specified for the AltPerson relation (lab04_1.sql), noting that no primary or foreign keys are specified at this point and that the data reflect the pathologies of the schema. The table is intended to represent information on people in the Centrepointe church domain, their mentors and their team memberships, e.g., Shamkant in the chair of the elders and the mentor of Ramez.

1. Explain, informally, why the relation is not well-designed and then prove your point formally.

2. Specify a properly normalized schema for this database.

3. [Homework] Implement an appropriately normalized version of the AltPerson relation by editing the existing command file, adding your own create-table commands below the ones already in the file and using different table names to avoid naming conflicts. Your commands should do the following.

   1. Create your normalized sub-relations. For simplicity's sake, don't introduce new attributes; just use the ones in the original table (perhaps repeated as foreign keys).
   2. Populate your new relations by querying the data from the existing AltPerson database. Note that you may need to use a DISTINCT query to get unique key values from the raw data.
   3. Write queries to display the data in your new sub-relations.

4. [Optional challenge] Write a query the reproduces the old data table from your new tables.

Specify your solutions to the conceptual questions in clearly-labeled comments in the original command file, then add your programmed solution for the homework.

Study the schema and sample data for persons, teams and visits (lab04_2.sql). The tables represent information on people, their (potentially many) team memberships and their (potentially many) church visits, e.g., Shamkant is an elder and he visited on Feb 22.

1. Demonstrate, formally, whether the relations implemented by the tables PersonTeam and PersonVisit are in BCNF and/or 4NF (or not).

2. Consider the output of the data queried by the combined “view” query at the end of the command file. Demonstrate, formally, whether this view, when considered as a (single) relation, is in BCNF and/or 4NF (or not).

3. The view has the same number of records as the original tables. Does this mean that the original schema and the derived “view” schema are equally appropriate? If so, explain why; if not, explain why one of the schemata is better. Does your choice depend on context?

4. [Homework] Create a new table to store the data queried by the combined “view” query at the end of the command file and load it with the queried data. Note that you can load the data either using hard-coded INSERT commands (for some credit) or by inserting data selected from the original tables (for full credit).

Specify your solutions to the conceptual questions in clearly-labeled comments in the original command file, then add your programmed solution for the homework.