Question

%20Capstone%20Project%20using%20Access.pdf t-content-rid-24664575-2/courses/209275/CIS%201 1 509620D Part 2 If your last name begins with the letter A-G: create a report that displays only the people who have paid at least $500 this year. Include the person's name, address, city, phone number, and YTD paid sorted by YTD paid (descending), then their last name. If your last name begins with the letter H-M: create a report that displays only the people who have a balance of more than $100. Include the person's name, address, city, phone number, and balance sorted by balance (descending), then their last name. If your last name begins with the letter N-S: create a report that displays the trainers for people who have paid less than $225 this year. Include the person's name, YTD paid, and the trainer's name sorted by YTD paid (descending), then the person's last name. If your last name begins with the letter T-Z: create a report that displays the trainers for people who have a balance of SO. Include the person's name, balance, and the trainer's name sorted by the person's last name, then the person's first name. queries, and/or reports should have appropriate names/titles. Name the file using your name. For example, John Doe would name the file DB-prj-Doe-lohn.accdb. Rubric Below are listed some of the more frequent mistakes that you can lose points for. This list is not all inclus Database not appropriately named. (-4) incorrect table definition. (-15) Tables not related. (-15) Missing key field. (-10) Incorrect key field. (-7) Incorrect related field. -7) Field missing. (-5) Related fields do not have same exact name, type, or size. (-5) Too few records in table. (-5) Incorrect field type. (-4) Field size is too large or small. (-3) Excessive redundancy. (-7) Missing query. (-20) Table, query, or report name does not describe content. (-5) Missing report.(-20) Report not sorted. (-7) Report not sorted by all criteria. (-4) Spelling error. (-2) Example There is no example for this project.

Answer 1

Syntax to create the database

CREATE DATABASE database_name;

So create a database

CREATE DATABASE Inperson_Fitness;

From the above table you can create two tables

1. client table

2. trainer table

To avoid redundancy we can include either unique key or primary key in both the tables.

To relate two tables we need primary key in one table and foreign key in another table.

Thus we will choose primary key to establish uniqueness in both the tables.

Syntax to create table.

CREATE TABLE table_name (
    column_name1 datatype primary key,
    column_name2 datatype,
    column_name3 datatype);

Include a new column to the client table as client_id for uniqueness and in the trainer table we will include trainer_id to establish a relationship between two tables.

CREATE TABLE client (
    client_id number(10) primary key,
    clientname char(25),
    address varchar2(15), city char(15), state char(2), phoneno varchar(15), YTD_paid varchar(5), balance varchar(5));

Note: The YTD_paid and balance field are declared as varchar since in the table the data includes $ in these fields. If the $ is not there we can change it as number.

CREATE TABLE trainer(
    trainer_id number(10) primary key,
    client_id number(10) foreign key references client(client_id)
    trainer_name char(25), hourly_pay varchar(10));

Note: The hourly_pay is declared as varchar since in the table the data includes $ in these fields. If the $ is not there we can change it as number.

Thus the table has been created with proper firld names and sizes and included the primary key.