Question

Can anybody help me with this?

Create an EER Diagram for the new database to be constructed.

Upload a completed lab report answering all questions and including the EER Diagram

Requirements and Information:

• 1. Each user must have an email address, first name, and last name.
  2. Each user can post one or more videos.
  3. Each video must have a filename and creation date/time.
  4. Each video should be associated with a product that is being demonstrated.
  5. A product can be associated with multiple videos.
  6. Each product must have a unique ID and a name.

Answer 1

Tool used: My sql workbench

1. Download mysqlworkbench from the web (Anyways it's open sourced)
2. Open mysql workbench
   MySQL Workbench -ox File Edit View Database Tools Scripting Help Welcome to MySQL Workbench MySQL Workbench is the official graphical user interface (GUI) tool for MySQL. It allows you to design, create and browse your database schemas, work with database objects and insert data as well as design and run SQL queries to work with stored data. You can also migrate schemas and data from other database vendors to your MySQL database. Browse Documentation > Read the Blog > Discuss on the Forums > MySQL Connections © ☺ MySQL Workbench could not detect any MySQL server running. This means that MySQL is not installed or is not running. Rescan servers
   
3. Click File -> New Model
4. Click Add Diagram
5. As soon as you click add diagram you can see an interface something like this
   
   - o * MySQL Workbench MySQL Model X File Edit View Arrange EER Diagram x Model Database Tools Scripting Help Modeling Additions Bird's Eyew Zoom: 100% - MWB timestamps create_time, update_time user MWB username, email, passwor... category category_id, name Catalog Tree mydb Tables Views Routine Groups Catalog Layers User Types Description Editor Description Properties Templates Ready
   

• Now you can start building the diagram by adding tables

Now by seeing the question let's list down all the possible tables.

• Each user must have an email address, first name, and last name.

So the table should have values something like this

Email Address (Primary Key)

LastName

FirstName

• Each video must have a filename and creation date/time.

FileName (PrimaryKey)

CreationDateTime

• Each product must have a unique ID and a name.

productid (PrimaryKey)

name

So we now know 3 tables

Let's get to know about the relations between these tables

• Each user can post one or more videos.
  i.e the relation between user and videos is one to many relation
• Each video should be associated with a product that is being demonstrated.
  The relation between video and product is Many to one relation
• A product can be associated with multiple videos.
  The relation between product and video is one to many relation

So now we have tables and it's relation.Let's head back to mysqlworkbench and start building the ER diagram

- 5 x MYSOL Workbench MySQL Model X Eile Edit View Arrange EER Diagram x Model Database Tools Scripting Help 90D Bird's Eye Diagram Modeling Additions Zoom: 100% a timestamps beste time, update time user wemame, email passwor category category id, name Catalog Tree mydb Tables Views Routine Groups Place a New Table (Quick key - press T) Catalog Layers User Types Description Editor Description Properties H > Templates Ready

Click on the table symbol and click again on the diagram=> as soon as you click a new table appears on the diagram (double click the table on the diagram)

• 
  - 5 x MYSOL Workbench MySQL Model X Eile Edit View Arrange EER Diagram x Model Database Tools Scripting Help 90 Diagram Modeling Additions Bird's Eye Zoom: 100% - a timestamps beste time, update time user usemame, emaill passwor category category id, name Catalog Tree tablei mydb Tables Views Routine Groups User Types Catalog Layers Description Editor table 1: Table Description Properties H . > Templates Placed tablet
  
  - o * MySQL Workbench MySQL Model X File Edit View Arrange EER Diagram x Model Database Tools Scripting Help @DO Bird's Eye Diagram Modeling Additions Zoom: 100% - MWB timestamps create_time, update_time user MWB username, email, passwor... category category_id, name Catalog Tree x mydb Tables Views Routine Groups table1 - Table x Table Name: table 1 Schema: mydb Column Name Datatype Default/Expression PK O NN O UQ B O UN O ZF O AIG O O O Catalog Layers User Types Description Editor table1: Table Column Name: Data Type: Charset/Collation: Default: Comments: Storage: Virtual Primary Key Binary Stored Not Null Unsigned Unique Zero Fill Auto Increment Generated Description Properties Columns Indexes Foreign Keys Triggers Partitioning Options Inserts Privileges Templates
  Now fill the tables with the values which we know(listed above)
• After creating all the tables let's make relation between them

- 0 X MySOL Workbench MySQL Model X Eile Edit View Arrange EER Diagram x Model Database Tools Scripting Help Modeling Additions Bird's Eyes Zoom: 100% II timestamps create_time, wpdate_time user username, email, passwor... category category_id, name Catalog Tree mydb Tables Views Routine Groups User Types Place a New 1:n Non-Identifying Relationship (Quick key press 2) Catalog Layers Description Editor : No Selection Properties > Templates Description Placed table

• click on this relation and next click on both the tables which satisfy this relation
• Similary do it for all the tables

Finally you end up with the diagram created as required

user email VARCHAR(45) firstnam e VARCHAR(45) lastname VARCHAR(45) H Indexes video filename VARCHAR(45) creationDateTime DATETIME user_email VARCHAR(45) product_productid INT --- * Indexes U product productid INT name VARCHAR(45) - - - - Indexes