Using MySQL, make a picture of an ERD using these guidelines:

Flatfile:

Statement of Work:

Thanks in advance!

Purpose: To design a simple database from data that has not been normalized. Task: Create an ERD from data about artist and their artwork and keywords that go with each piece of art. Instructions 1. Read through the Statement of Work for the Virtual Art Gallery 2 to understand the database you will be designing and to understand the project we are starting with this homework. 2. Using the Art Flat File Data determine the entities you will use for each table of your database. 3. Determine which attributes will belong to which entity. Also, according to the statement of work, determine which data might be entities and which can remain as attributes. Don't forget to add a primary key to each entity. 4. Determine how the tables will be related to each other. Resolve any many-to-many relationships with a linking table. 5. Create an ERD model in workbench and give it the name of 'art'. 6. Add the tables for each entity. Use lower-case and no spaces for all table and attribute names. 7. Add the attributes and appropriate data types for each table. You will not add foreign keys yourself as column or attribute names. They will be added by Workbench when you determine the relationship in the next step. You don't have to auto-increment the primary key if you don't want to. Some attributes may need to have the option of being left null. 8. Add the relationships between the tables and you will see the foreign keys added automatically. Make sure the many sides of the relationship has the foreign key. Remember, you shouldn't have table prefixes on foreign key names (meaning the foreign key and primary key should have the same name) since we changed the Workbench preferences. If the two names are different, refer back to the videos in Week 4. 9. Submit a screenshot or image of the Entity-Relationship Diagram (ERD). To save the model you can save with a File - Save Model, it will create an mwb file for you. Do not turn in the.mwb file, however. It does not always open properly for the grader. Please submit the screenshot or a png file using File - Export - Export as PNG. mname fname Vincent year period type file keyword Iname van Gogh 1889 Impressionism Oil 1889 Post-Impressionism Oil 1888 Post-impressionism Oil irises.jpg starrynight.jpg sunflowers.jpg flowers blue, landscape flowers Rembrandt Harmenszoon van Rijn 1642 1633 Baroque Oil Dutch Golden Age Oil nightwatch.jpg stormgalilee.jpg girl, people, battle boat, water, people, Christ Leonardo da Vinci dob dod country local title 1853 1890 France n Irises The Starry Night Sunflowers 1606 1669 Netherlands n Night Watch Storm on the Sea of Galilee 1452 1519 Italy n Head of a Woman Last Supper Mona Lisa 1965 United States y Hillside Stream Old Barn 1970 United States ly Beach Baby 1840 1926 France n Women in the Garden 1904 1973 Spain n Old Guitarist 1475 1564 Italy n 1508 High Renaissance 1498 Renaissance 1517 Renaissance Oil Tempra Oil headwoman.jpg lastsupper.jpg monalisa.jpg girl, people food, people, Christ girl, people Venture Lonzo Coy 2005 1992 Modern Modern Oil Oil hillsidestream.jpg water, landscape oldbarn.jpg landscape Deborah Gill 1999 Modern Watercolor beachbaby.jpg water, people, baby Claude Monet 1866 Impressionism Oil womengarden.jpg landscape, people, flowers Pablo Picasso 1904 Modern Oil guitarist.jpg blue, people Michelangelo di Lodovico Simoni Statement of Work (SOW) for Interactive Art Gallery 1. History The business is an art museum with interactive art and technology. They need help with their virtual art gallery. This virtual gallery has over 300 pieces of art from important national collections. Images show their current system. The Virtual Gallery includes 6 large touch screens that runs a custom-built system that allows users to search, browse and examine images of around 300 artworks. So the output the business requires are these pieces of art shown visually on a large touch screen. Along with the artwork, information about each piece is displayed such as the artist, type of medium, year of creation, etc. The user can search by artist, country, keywords, and style or period of painting. WELCOME TO THE ARTitorium VIRTUAL GALLERY Auguste Renoir 1841-19, French ons with a Ca 107 name 21.11.1 WHAT WOULD YOU LIKE TO SEE? National Croft Gift of and Benjamin E View artworks VIRTUAL GALLERY: Abstract Expressionism VIRTUALGALLERY A SHORT TOUR THROUGH ALONG HISTORY OF ART The collection ambas programs who years and consume Exchanted by one or more pich the National Galery of monian ARTISTS CHRONOLOGY STYLES WHAT DO YOU WANT TO ME Choose the lower ARTISTSNamen was -STYLE. Ramos, SUBJECT ... TIMELINE of years -NATIONALITY-American French RANDOM WWho LEO HOW DO YOU WANT TO BE the PLAY . astub SOVES SUBJECTS NATIONS TIMELINE RANDOM ERORE FURTHER ZOOM on a pour SAD by TEXT DIPERIMENT LET VIRTUAL GALLERY WHAT The limitations of the system are as follows: The owner of the art museum does not have the ability to add her own artwork to the system. She cannot add local artwork to the system and feels that this is important. She wants the ability to add her own artwork no matter the artist or where they are from and to also be able to edit current information in the virtual gallery. The users get frustrated because they expect the large touch screen to interact like a common touch device to do things such as zooming in on the artwork by spreading apart two fingers or zooming out by pinching their fingers and the system does not allow these type of multi-touch capabilities. Currently the system runs independently on each machine. Having one location control all screens would be helpful so changes will effect each machine automatically once an addition or edit takes place. Currently the information resides in each machine independently and the gallery has no way to manage the system themselves. They must rely on representatives to come or make any changes. There are a lot of features that do not need to be on the system. The owner feels like it could be simplified and thus be more user friendly. Like limiting extraneous output to the user on the screen and a few of the methods to search are rarely if ever used. 2. Scope The owner would like her own dedicated server to house their own database of information for the virtual gallery. This system would be accessible to management for many additions or edits to the data of the system. The art gallery will purchase this server. Web Application Development team: The need is a web-based application that will allow the user to have the multi-touch experience she is looking for. However, the web application does not need to be live on the web. It will be a local web application that only runs locally at the art gallery. Therefore, the scope for security will not need to be as extensive as it would have to be if it were online. There will be security and access privileges needed however so only those that can edit, delete or add to the gallery are limited and have a secure authentication logins. Database team: The database will need to be designed and data will need to be entered into the system. Specific queries will need to be created to not only search information using user criteria, but also insert, update, and delete queries to allow management to add, edit and delete artwork themselves. This is the job of the database team. They will use MySQL Workbench to create this database system. Users need to have output that includes the types of data as shown in the associated flat files. This will allow the user to search artwork by artist, by artist's country, by keyword or subject, and by period or style of artwork. Other information will be shown once the artwork is displayed like the type (medium it is created with). To simplify the system, there will only be one value needed for the country of the artist. No future expansion is needed and therefore, no country table is needed. There will also only be one value for the period of the art and the art type. There is no need for future expansion on either of these topics associated with the artwork. For example, the value of 'Baroque' or the value of 'Oil, would be attribute values within the entity of the artwork; not attribute values in a 'time period' or 'art medium' table. This is an example of denormalizing according to the simplified needs of this system. When choosing a datatype for the filename; it is just text representing the name of the file that will come in with the web application front end. Both teams: The web application, that is the user interface, will be created by a separate web development team using HTML, CSS, JavaScript and PHP. The queries created by the database team will be integrated into this web system to work together. 3. Timeline Database Team: We have two weeks to design and add a small amount of data to the system to begin testing. Future weeks for developing the proper queries for the end user and for the backend management of the system where admins can add, edit and delete data. Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Design, Data Entry, Update Delete Select queries for end user Web Development Team: Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Wireframe & Design HTML, CSS, PHP coding development Purpose: To design a simple database from data that has not been normalized. Task: Create an ERD from data about artist and their artwork and keywords that go with each piece of art. Instructions 1. Read through the Statement of Work for the Virtual Art Gallery 2 to understand the database you will be designing and to understand the project we are starting with this homework. 2. Using the Art Flat File Data determine the entities you will use for each table of your database. 3. Determine which attributes will belong to which entity. Also, according to the statement of work, determine which data might be entities and which can remain as attributes. Don't forget to add a primary key to each entity. 4. Determine how the tables will be related to each other. Resolve any many-to-many relationships with a linking table. 5. Create an ERD model in workbench and give it the name of 'art'. 6. Add the tables for each entity. Use lower-case and no spaces for all table and attribute names. 7. Add the attributes and appropriate data types for each table. You will not add foreign keys yourself as column or attribute names. They will be added by Workbench when you determine the relationship in the next step. You don't have to auto-increment the primary key if you don't want to. Some attributes may need to have the option of being left null. 8. Add the relationships between the tables and you will see the foreign keys added automatically. Make sure the many sides of the relationship has the foreign key. Remember, you shouldn't have table prefixes on foreign key names (meaning the foreign key and primary key should have the same name) since we changed the Workbench preferences. If the two names are different, refer back to the videos in Week 4. 9. Submit a screenshot or image of the Entity-Relationship Diagram (ERD). To save the model you can save with a File - Save Model, it will create an mwb file for you. Do not turn in the.mwb file, however. It does not always open properly for the grader. Please submit the screenshot or a png file using File - Export - Export as PNG. mname fname Vincent year period type file keyword Iname van Gogh 1889 Impressionism Oil 1889 Post-Impressionism Oil 1888 Post-impressionism Oil irises.jpg starrynight.jpg sunflowers.jpg flowers blue, landscape flowers Rembrandt Harmenszoon van Rijn 1642 1633 Baroque Oil Dutch Golden Age Oil nightwatch.jpg stormgalilee.jpg girl, people, battle boat, water, people, Christ Leonardo da Vinci dob dod country local title 1853 1890 France n Irises The Starry Night Sunflowers 1606 1669 Netherlands n Night Watch Storm on the Sea of Galilee 1452 1519 Italy n Head of a Woman Last Supper Mona Lisa 1965 United States y Hillside Stream Old Barn 1970 United States ly Beach Baby 1840 1926 France n Women in the Garden 1904 1973 Spain n Old Guitarist 1475 1564 Italy n 1508 High Renaissance 1498 Renaissance 1517 Renaissance Oil Tempra Oil headwoman.jpg lastsupper.jpg monalisa.jpg girl, people food, people, Christ girl, people Venture Lonzo Coy 2005 1992 Modern Modern Oil Oil hillsidestream.jpg water, landscape oldbarn.jpg landscape Deborah Gill 1999 Modern Watercolor beachbaby.jpg water, people, baby Claude Monet 1866 Impressionism Oil womengarden.jpg landscape, people, flowers Pablo Picasso 1904 Modern Oil guitarist.jpg blue, people Michelangelo di Lodovico Simoni Statement of Work (SOW) for Interactive Art Gallery 1. History The business is an art museum with interactive art and technology. They need help with their virtual art gallery. This virtual gallery has over 300 pieces of art from important national collections. Images show their current system. The Virtual Gallery includes 6 large touch screens that runs a custom-built system that allows users to search, browse and examine images of around 300 artworks. So the output the business requires are these pieces of art shown visually on a large touch screen. Along with the artwork, information about each piece is displayed such as the artist, type of medium, year of creation, etc. The user can search by artist, country, keywords, and style or period of painting. WELCOME TO THE ARTitorium VIRTUAL GALLERY Auguste Renoir 1841-19, French ons with a Ca 107 name 21.11.1 WHAT WOULD YOU LIKE TO SEE? National Croft Gift of and Benjamin E View artworks VIRTUAL GALLERY: Abstract Expressionism VIRTUALGALLERY A SHORT TOUR THROUGH ALONG HISTORY OF ART The collection ambas programs who years and consume Exchanted by one or more pich the National Galery of monian ARTISTS CHRONOLOGY STYLES WHAT DO YOU WANT TO ME Choose the lower ARTISTSNamen was -STYLE. Ramos, SUBJECT ... TIMELINE of years -NATIONALITY-American French RANDOM WWho LEO HOW DO YOU WANT TO BE the PLAY . astub SOVES SUBJECTS NATIONS TIMELINE RANDOM ERORE FURTHER ZOOM on a pour SAD by TEXT DIPERIMENT LET VIRTUAL GALLERY WHAT The limitations of the system are as follows: The owner of the art museum does not have the ability to add her own artwork to the system. She cannot add local artwork to the system and feels that this is important. She wants the ability to add her own artwork no matter the artist or where they are from and to also be able to edit current information in the virtual gallery. The users get frustrated because they expect the large touch screen to interact like a common touch device to do things such as zooming in on the artwork by spreading apart two fingers or zooming out by pinching their fingers and the system does not allow these type of multi-touch capabilities. Currently the system runs independently on each machine. Having one location control all screens would be helpful so changes will effect each machine automatically once an addition or edit takes place. Currently the information resides in each machine independently and the gallery has no way to manage the system themselves. They must rely on representatives to come or make any changes. There are a lot of features that do not need to be on the system. The owner feels like it could be simplified and thus be more user friendly. Like limiting extraneous output to the user on the screen and a few of the methods to search are rarely if ever used. 2. Scope The owner would like her own dedicated server to house their own database of information for the virtual gallery. This system would be accessible to management for many additions or edits to the data of the system. The art gallery will purchase this server. Web Application Development team: The need is a web-based application that will allow the user to have the multi-touch experience she is looking for. However, the web application does not need to be live on the web. It will be a local web application that only runs locally at the art gallery. Therefore, the scope for security will not need to be as extensive as it would have to be if it were online. There will be security and access privileges needed however so only those that can edit, delete or add to the gallery are limited and have a secure authentication logins. Database team: The database will need to be designed and data will need to be entered into the system. Specific queries will need to be created to not only search information using user criteria, but also insert, update, and delete queries to allow management to add, edit and delete artwork themselves. This is the job of the database team. They will use MySQL Workbench to create this database system. Users need to have output that includes the types of data as shown in the associated flat files. This will allow the user to search artwork by artist, by artist's country, by keyword or subject, and by period or style of artwork. Other information will be shown once the artwork is displayed like the type (medium it is created with). To simplify the system, there will only be one value needed for the country of the artist. No future expansion is needed and therefore, no country table is needed. There will also only be one value for the period of the art and the art type. There is no need for future expansion on either of these topics associated with the artwork. For example, the value of 'Baroque' or the value of 'Oil, would be attribute values within the entity of the artwork; not attribute values in a 'time period' or 'art medium' table. This is an example of denormalizing according to the simplified needs of this system. When choosing a datatype for the filename; it is just text representing the name of the file that will come in with the web application front end. Both teams: The web application, that is the user interface, will be created by a separate web development team using HTML, CSS, JavaScript and PHP. The queries created by the database team will be integrated into this web system to work together. 3. Timeline Database Team: We have two weeks to design and add a small amount of data to the system to begin testing. Future weeks for developing the proper queries for the end user and for the backend management of the system where admins can add, edit and delete data. Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Design, Data Entry, Update Delete Select queries for end user Web Development Team: Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Wireframe & Design HTML, CSS, PHP coding development