Develop a relational Database in MS Access. The database must have a minimum of THREE tables. Each database must also have one input form and one report. The inpu

Database Lab Page 8

Develop a relational Database in MS Access. The database must have a minimum of THREE tables. Each database must also have one input form and one report. The input form and report must be fed by a query, and not the tables.

Step 1: Define the problem that requires the database as a solution. The Key to GOOD DB development is to DEFINE – DEFINE – DEFINE!!!!

Step 2: Design the database. Develop the ERD of the database you will build in MS Access.

The ERD must show:

1. Entities (people, places, things, concepts). Entities equate to tables.

2. Attributes – the characteristics of the entities. Attributes equate to columns.

3. Primary Keys – the identifiers of the various instances of the entities (shown by a solid

underline of the attribute).

4. Foreign Keys – the way Entities are “linked” through the relationships (shown by a

dashed underline of the attribute or with a “(FK)” next to the name of the attribute).

5. Cardinality – the types of relationships between the entities (1:1, 1:N, M:N). Use crows

feet to show the many side of the relationships.

ERD Reminders:

1. Primary Keys migrate to the many side of the relationship to become foreign keys.

2. Many to Many (M:N) relationships become another table in the database (see example).

3. Do not model users of the database or outputs (reports) of the database.

4. Do not use plurals in naming entities (use STUDENT not STUDENTS).

5. Name the relationships.

6. You may have to use concatenated primary keys.

You can use ERDPlus to create your DB. This program can be used on a MAC or PC.

https://erdplus.com/ Use the StandAlone button to create. See Diagrams Below.

Note: setting up erdplus to make the relationship go in the correct direction is not intuitive (in fact it seems backwards. Play with it to get it right.).

Final Product

Step 3: Develop the relational database in MS Access. Populate the database with straw man. You need enough to test the database. I recommend at least five to ten lines in each table. You can use your forms to load the data.

There are possible problems with Referential Integrity as well, so you may want to wait until the tables are populated to make the relationships in the database. Also, when dealing with Referential Integrity, you must have a Primary Key (in the one table) that MATCHES a Foreign Key (in the many table).

Example

Example 1: M:N relationships become a table in the database. NOTE: in this case a CASE tool was used to develop the model, and the tool uses PK to designate Primary Keys and FK to designate Foreign Keys. Notice the concatenated (compound) keys for Tables COURSE and SECTION.

Example Completed Database in MS Access Above .

This is the DDL that was used to create the database example. It is meant only for reference as to how it works. You are not responsible for it.

CREATE TABLE COURSE(

Course_Prefix TEXT(10) NOT NULL,

Course_Number TEXT(10) NOT NULL,

Course_Name TEXT(10) NOT NULL,

Course_Hours TEXT(10) NOT NULL)

ALTER TABLE COURSE ADD

CONSTRAINT COURSE_PK PRIMARY KEY (Course_Prefix,Course_Number)

CREATE TABLE SECTION(

Call_Number TEXT(10) NOT NULL,

Semester TEXT(10) NOT NULL,

Year TEXT(10) NOT NULL,

Room_Number TEXT(10) NOT NULL,

Grades TEXT(10) NOT NULL,

Student_ID TEXT(10) NOT NULL,

Course_Prefix TEXT(10) NOT NULL,

Course_Number TEXT(10) NOT NULL)

ALTER TABLE SECTION ADD

CONSTRAINT SECTION_PK PRIMARY KEY (Call_Number,Semester,Year,Student_ID,Course_Prefix,Course_Number)

CREATE TABLE STUDENT(

Student_ID TEXT(10) NOT NULL,

Student_Name TEXT(10) NOT NULL,

Student_Address TEXT(10) NOT NULL,

Student_City TEXT(10) NOT NULL,

Student_State TEXT(10) NOT NULL,

Student_Zip TEXT(10) NOT NULL,

Student_Telephone TEXT(10) NOT NULL)

ALTER TABLE STUDENT ADD

CONSTRAINT STUDENT_PK PRIMARY KEY (Student_ID)

ALTER TABLE SECTION ADD

CONSTRAINT enroll_in FOREIGN KEY (Student_ID)

REFERENCES STUDENT (Student_ID)

ALTER TABLE SECTION ADD

CONSTRAINT consist_of FOREIGN KEY (Course_Prefix,Course_Number) REFERENCES COURSE (Course_Prefix,Course_Number)

Rubric:

ITEM	Points	Comments
Definition	15	Students will create a problem statement and also define the attributes of their tables.
ERD	15	The students will CREATE the logical Entity Relationship Diagram for the solution to their problem statement.
SQL (DDL)	5	Students will create the DDL to create their Database. Note: the SQL statements to create the table can be created using ERDPlus.
Tables	15	Student will create at least three tables in their database. 5 points per table.
Attributes	5	Students will create the attributes for each table. These are the columns of the tables.
Relationships	5	Students will create the appropriate relationships between tables. This can be done using SQL or by using the GUI.
PK/FK	5	Each parent table must have a PK. Each child table must have both a PK and an FK.
Query	5	Students will design a query from their tables.
Form	5	Students will design a form for inputting data into their tables.
Report	5	Students will design a report to get INFORMATION out of the database.
Straw Man Data	5	At a minimum, each table will have 5 or more instances of data.
DB Functionality	15	The database has to work folks!
TOTAL	100

The Items Definition through PK/FK can be done in the erdplus.com site. Make sure you copy the items as it says in the tutorial. Then, all you will have left to do in MS Access is the Query through Straw Man Data. If you get all of the items complete up to the DB Functionality, then your database works! The library has MS Access on the computers, if you would like to work there as opposed to copying the open source software. I am also attaching a document to this assignment that has 3 videos to show how to create the query, form and the report. They are very helpful videos. Here is a site that has great ideas for database projects:

https://www.lovelycoding.org/2013/11/top-18-database-projects-ideas-for.engineering-bca-mca-btech-bsc.html

8

100 Points Total

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.