Checkpoint III: Database Architecture (7 points) In this checkpoint, students will build the database architecture based on their ERD and entity description from checkpoint II. Entity Establishment Relationship Diagram (EER) Note:

This section must be done using MySQLWorkBench Create an EER model of your database system based on the final version of your ERD and entity descriptions. Your EER must show clearly the following components: .PK, FK and Unique keys (if any) • All the table attributes, and their data types • NOT NULL in attributes (if any) • Identifying relationships and non-identifying relationships. Both of them represented by solid and dashed lines in the database model. • All the relationships between entities and their cardinalities including those with zeros Once the EER is completed do the following: (1) save your database model as EER.mwb and upload this file to the "files" folder located in this directory (2) export your EER to any of the following formats (pdf, png, jpg....) and embed it into your milestone1.pdf file for this checkpoint. Screenshots of your EER are also allowed as long as the image is readable and high quality. If we can't read your ERD, no credit will be given for this checkpoint* Constraints Description In your document, create a description table (similar format to the one below) including all the tables that implement ON DELETE AND ON UPDATE and all their possible constraints (CASCADE, SET NULL......). This description should include a detailed comment about your motivations to select those tables as the ones that implement those constraints. Table FK ON DELETE ON UPDATE Account user ON CASCADE ON CASCADE User role SET NULL ON CASCADE Comment If a user is deleted, then the account from that user must be deleted as well If a role is removed from a specific user, the user that was holding that role will hold no role until a new one is assigned./nForward Engineering and Populating Database Note that all your work done in this section must be uploaded in the file's folder found in this directory The forwarding engineering process is the one that translates your database model (EER) into the database schema that is used to create the physical database, tables and attributes in your database system. Note that this process must be done in MYSQLWorkBench as seen in class. Backward engineering is not allowed in this section Before starting the forwarding engineering process of your database model, make sure to provide a meaningful name for your database schema. By default, MSQLWorkBench assign 'mydb'. Replace it with the name of your database + "DB" (i.e. LibraryManagementDB) Proceed with the forward engineering process of your database model as learned in class, and create a file databasemodel.sql file that contains all the CREATE SQL statements from the forward engineering process Run databasemodel.sql script in your mysql instance to create your physical database schema and its tables in your system (localhost). This can be done in many ways, but the most common ones are: (1) Man the databasemodel.sql script directly in your mrskorkBench or (2) Open a SQL script windows MySQLMorkBench, and copy and paste the content of databasemodel.agl there. Then, click on run. (3) Open a terminal windows, and connect to your Mysql Sestance, the execute the following command: mysql> source ; Once your database and tables are created in your mysql localhost instance, then upload the databasemodel.sql file in the 'files' folder located in this directory No need to add anything to the pdf document for this section. Note that at grading time your databasemodel.sql must run without errors to get credit for this section Inserting Sample Data In order to test your database system, it needs to be populated first with sample data that represents the scope and domain of the business requirements that will be implemented by the software product that is using your database system. 1. Create a file inserts.sql file. 2. Add some comments on the top of the file to explain the context of this file 3. The first SQL code this file must have is the following:/nUSE LibrarySystem:8; Replace LibrarySystems with the name of your database system 4. Insert some sample data in all your tables. Each table in your database must have at least three inserts. For instance, our library system database has three tables User, Book, and Account: - Script name: Inserts.sel Author: Jose Ortiz insert sample data to test the integrity of this database system the database used to insert the data inte USE LibrarySystem; **User table inserts INSERT INTO User (user_56, name, dob) VALUES (1, 'Alice', 631152009), (2, 'Bob', 694742460), (3, "Truds', 958889600); --Book table Inserts INSERT INTO Book (ISBM, title, author) VALUES (87736778838, 'Database System Concepts', 'Avi Silberschatz¹), (8773677883 **Account table inserts INSERT INTO Account (account_id, user, role) VALUES (1, 1, 'Admin'), (2, 2, "user"), (3, 3, "admin"); 5. Run inserts.sql in your MySQL instance. 6. Upload the inserts.sql file in files' folder located in this directory No need to add anything to the pdf document for this section. No need to add anything to the pdf document for this section. Note: inserts.sql must run without errors in order to get credit for this section