: Based on the given Access database file from the class, the "Cupcake Shop", created an ERD diagram.

In the "All Access Object pane", there were total 6 tables, which means that I should create 6 tables for the ERD diagram.  

From the "Design View", you can find all the field names. 

Below this, image #1 to #6 are the given Access database file.

Picture# 1

Picture# 2

Picture# 3

Picture# 4

Picture# 5

Picture# 6

In the design view, the first field is always regarded as the primary key in all the tables.  

Go simply check the relationships among tables to the "Table Design"> "Relationships".

Can see that there was one missing table, the "customer_note" table  in the "Relationship" compare to the tables in the "All Access Objects" pane. 

Picture# 7

Represented the original relationship of the given file.

Picture# 8

Can simply add the "customer_note" table from the "Relationship" by dragging the table from the "All Access Objects" pane. Below this, the image represented the result. 

• Created ERD table with draw.io diagram from the web (link: https://www.diagrams.net/). 

Primary keys(PK) have to be bolded and underlined in the table.

Foreign keys(FK) have to be italicized in the table.

• Create relationships between tables.

Have to care about each relationships between two tables:

1. Cardinality, which is about the record x has single/multiple relationship with the record y, and vice versa.   

2. Participation, which is about the record x is must required or optional about the record y, and vice versa.

• There are three general types of relationships:

1. One to many (1:M), which is mostly common relationship.

2. One to one (1:1)

3. Many to many (M:N)

In this case, there were all one to many (1:M) relationships between the tables. 

Because, 

One customer_id  can create many orders and notes, between the sale_id .

Also,

One customer_id  can create many orders and notes, between the note_id.

Therefore, it is one to many (1:M) relationships.

Each  sale_id, which has created by one customer (customer_id) can create many payment_id. 

Also,

Each sale_id, which has created by one customer (customer_id) can create many sale_item_id. 

Therefore, it is one to many (1:M) relationships.

One Product_id is has to be unique because this identify about a product, and it could be inuded in many sale_item list. 

Therefore, it is one to many (1:M) relationships.

As a result, created the ERD diagram like below this,  

: Based on the given Access Database file from the class,  edit the file like creating and putting more tables, fields, and basic UI such as forms. 

Step 1

: Downloaded the Access Database file which is given by the instructor in the class. 

Step 2

: Opened and changed schemas. Below this is the image, which represented the given Access Database file:

Picture# 1

Represented the original relationship of the given file.

1. Added a quantity field to bridge table which consists of items for each sale. 

According to the original relationship, the product was identified as the item. The sale_item table was bridged to between the sale and product tables, and it looked appropriate for adding the quantity field.

2. Added an unit_capacity_per_day table to the product table, which would consists of the each particular types of cupcakes that  the stores can produce per day.

Picture# 2

It's result from the 1 and 2. 

3. Added the "event" table, which for event catering. Put PK fields ( customer_id, sale_id, event_datetime, bake_order_datetime, description, delivery_instructions, and notes) from the "Design View".

Also, I put proper DataTypes for each different fields. Through the suggested "Feild Name", I could assumed the appropriate "Data Type". 

Picture# 3

it is the result from the 3.

Step 3

: Added user interface.

1. By clicking the "Form" in the "Forms" in the menu, can create forms. According to the direction, I created a new form for the customer table. Firstly, I opened the customer table in the "Datasheet View", and clicked the "Form" in the "Create" menu. Next, I closed the customer table, and saved the form as "customer".

Picture# 4

The "Datasheet View" for the customer table.

Picture# 5

Shows the result about the creating of the customer form. According to the customer form, can see the easy and simple graphical user interface for users. From this customer form, user can edit the data in the customer table except the customer_id (Which is the primary key, the super key). 

2. Created the new form for the sale and sale_item tables.

2-1. Using "Form Wizard", can create a form for the sale and sale_item tables. Set the sale table for the master form, and select all fields by clicking the ">>"     button in the middle of the pane. After that pick the sale_item table as subform, and select "product_id", "quantity" fields, and click the "Next" button.     Below this, image #6 and #7  are visually representing those steps. 

Picture# 6

Can specifically select the fields, which one user want to use for the efficient user interface. 

Picture# 7

In this case, the "sale_item" table was planned to use as subform. Firstly, consider about which additional data is going to be proper for the master form about the sale table. Clicked the "Next" button.

Picture# 9

Selected the "Datasheet" layout for the subform, which is about the sale_item table. Clicked the "Next" button. 

Picture# 11

Result for the sale form as a "Master Form" in the "Design View".

Result for the "Subform" about the sale_item form in the "Design View".

2-2 Replaced the customer_id Foreign Key  field with the last_name and first_name by using the query. 

Picture# 13

Clicked the sale form in the "Design View". Go to the "Form Design" in the Menu. Select the "Combo Box", and created  the "Combo Box"  by dragging the

cursor.

Picture# 14

Started the "Combo Box Wizard", and selected the next options by user's object. In this case, user needed to bring the values from customer table to the sale table. Next, selected last_name field, and chose a sort order type. In the last step for the Wizard, named a combo box for the efficient management later. 

Picture# 15

Clicked the "Combo Box", and went to "Property Sheet". Next, click the "..." on the "Row Source".

Picture# 16

Went to the "Query Builder" and change the second "last_name field to "last_name & ", " & first_name", a query that concatenate the last_name and the first_name fields. Next, clicked the "Run" button.

Picture# 17

Go to  "Form View", can see the Smith, Barb. Smith is the last name, and Barb is the first name. Can check the name from the customer table. 

Lastly, can change and edit the layout and design of the forms from the "Design View"

Creating tables in MySQL. 

First of all, needed to count that how many tables need to create. 

Picture# 1

According to the  "All Access Objects" pane, there were total 7 tables needed to create. 

Picture# 2

According to the given Access file, can check the each datatype for the tables from the "Design View". 

Access uses different data types with MySQL. If Access uses the "Short Text" than MySQL uses "varchar(#)"  or "TINYTEXT". Critically, MySQL did not have data type which is equivalent to the currency in the Access.

Here is my code:

create database annandale_cupcakes;   //"create database" is a statement that created  a database in  MySQL.
use annandale_cupcakes; //"use" is a statement that declares that MySQL is going to use the named database.
create table customer ( //"create table" statement  that used for creating a new table in MySQL.
customer_id int primary key auto_increment,  //"int primary key auto_increment" statement  creates  primary key.  Because primary keys  are integers like
first_name varchar(50), //1,2, 3, 4, 5..., and I used "int" before the  "-primary key..."
last_name varchar(50), //For the "last-name", used the "varchar(#)" because  name is absolutely  type of text.
phone_number varchar(20), //For the phone number, used the "varchar(#)" statement because  if  users put  symbols, then it would be counted as text data.
email varchar(30),
street varchar(100),
city varchar(50),
state varchar(25),
zip varchar(10),
notes varchar(255),
created_at datetime, //MySQL uses the "DATETIME" statement than Access uses the "Date/Time" for the date and time data types.   
updated_at datetime
);
create table customer_note (
note_id int primary key auto_increment,
subject varchar(50),
note varchar(255),
customer_id int,
created_at datetime,
updated_at datetime
);
create table event (
event_id int primary key auto_increment,
customer_id int,
order_id int,
event_datetime datetime,
bake_order_datetime datetime,
description varchar(255),
delivery_instructions varchar(255),
notes varchar (255)
);
create table payment (
payment_id int primary key auto_increment,
payment_method varchar(50),
amount decimal(5,2) not null, //MySQL uses "decimal(##,##)" statement to represent the currency .
notes varchar(50),   //With Image #4, it would explains how the "decimal(##,##)". statement
sale_id int,
created_at datetime,
updated_at datetime
);
create table product (
product_id int primary key auto_increment,
cupcake_type varchar(50),
cupcate_name varchar(50),
price decimal(5,2) not null,
cost_per_unit decimal(5,2) not null,
notes varchar(255),
unit_capacity_per_day int,
created_at datetime,
updated_at datetime
);
create table sale (
sale_id int primary key auto_increment,
customer_id int,
sale_date datetime,
notes varchar(255),
created_at datetime,
updated_at datetime
);
create table sale_item (
sale_item_id int primary key auto_increment,
sale_id int,
product_id int,
quantity int,
created_at datetime,
updated_at datetime
); //end of the statement.

Picture# 3

MySQL uses the "decimal(##,##)" statement to represent the currency. 

Picture# 4

This is the result, created total seven tables based on the given "Access Database". 

Since installed ODBC Driver on my computer(which has to be installed Access program), created a file with dsn file extension. 

For example. annadale_cupcakes.dsn

If created the dsn file with Notepadd++, can simply create the dsn file without confusion just putting ".dsn" at the end of your file name. 

Picture# 1

Copied this template from the class, and edited some information. The "UID" was equal to the master database username that you can find from the MySQL workbench. The "PWD" was standing for the password, that has to match with the password for the MySQL workbench. Also, "SERVER" has to match with the MySQL workbench as well, the IP was individually assigned for this project in the class. The "DATABASE" can be freely created by user. 

For connect the MySQL workbench and Access file, need to open the .dsn file. 

Go to "External Data" in Access program>"New Data Source"> "From Online Services"> "ODBC Database"> open the created ".dsn" file> select tables for the purpose. 

Picture# 2

Highlighted all the steps for opening the .dsn file. For the result, globe icons appear as tables.    

Picture# 3

Can edit and modify the shared database from Access file and MySQL workbench.  (see Picture#3 &4)

Picture# 4