Please see instructions Course book provided and additional instruction
Table 1 1. Create a MySQL database table named “tbldvdtitles.” You will be adding information about a minimum of four of your favorite movies. Your table should have the following structure: Field name Type Attributes asin varchar(15) primary key title varchar(100) price double(5,2) ASIN is an acronym for "Amazon Standard Identification Number." It is a primary key that Amazon uses for all of its products. You can find ASINs on the product description page on Amazon's web site. This link shows you where to find the ASINs: https://www.amazon.com/gp/help/customer/display.html?nodeId=200202190#find_asins 2. Write a SQL statement to add the ASIN, price, and title for your favorite movies. Table 2 1. Create a second database table named “tbldvdActors.” Your table should have the following structure: Field Type Attributes actorID int(5) auto_increment, primary key fname varchar(20) lname varchar(20) 2. Write a SQL statement to add at least four actors, one from each of your listed movies. 3. Write a SQL statement to update the last actor fname, and lname information. 4. Write a SQL statement to delete the first actor in the tbldvdActors table. Table 3 1. Create a third database table of relationships between actors and movie titles. Your table should have the following structure: Field Type Attributes asin varchar(15) primary key (composite) actorID int(5) primary key (composite) (Because this table uses a composite key, the delete statement must reference both the asin and actorID fields.) 2. Add data that describes the relationship between your movies and actors. Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 Database Design Process • Data analysis and requirements • Entity Relationship modeling and normalization • Data model verification • Distributed database design* • Determine end-user views, outputs and transaction requirements • Define entities, attributes, domains and relationships • Draw ER diagrams; normalize entity attributes • Identify ER modules and validate insert, update, and delete rules • Validate reports, queries, views, integrity, access, and security • Define the fragmentation and allocation strategy DBMS and Hardware Independent DBMS Dependent Hardware Dependent • Determine DBMS and data model to use • Define tables, columns, relationships, and constraints • Normalized set of tables • Ensure entity and referential integrity; define column constraints • Ensure the model supports user requirements • Define tables, indexes, and views’ physical organization • Define users, security groups, roles, and access controls • Define database and query execution parameters • Map conceptual model to logical model components • Validate logical model using normalization • Validate logical modeling integrity constraints • Validate logical model against user requirements Conceptual Design DBMS Selection Select the DBMS Logical Design Section Stage Steps Activities Physical Design 9-5 9-4 9-6 9-7 * See Chapter 12, Distributed Database Management Systems + See Chapter 11, Database Performance Tuning and Query Optimization • Define data storage organization • Define integrity and security measures • Determine performance measures+ Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. BUSINESS RULES Properly document and verify all business rules with the end users. Ensure that all business rules are written precisely, clearly, and simply. The business rules must help identify entities, attributes, relationships, and constraints. Identify the source of all business rules, and ensure that each business rule is justified, dated, and signed off by an approving authority. DATA MODELING Naming Conventions: All names should be limited in length (database-dependent size). ENTITY NAMES: Should be nouns that are familiar to business and should be short and meaningful Should document abbreviations, synonyms, and aliases for each entity Should be unique within the model For composite entities, may include a combination of abbreviated names of the entities linked through the composite entity ATTRIBUTE NAMES: Should be unique within the entity Should use the entity abbreviation as a prefix Should be descriptive of the characteristic Should use suffixes such as _ID, _NUM, or _CODE for the PK attribute Should not be a reserved word Should not contain spaces or special characters such as @, !, or & RELATIONSHIP NAMES: Should be active or passive verbs that clearly indicate the nature of the relationship Entities: Each entity should represent a single subject. Each entity should represent a set of distinguishable entity instances. All entities should be in 3NF or higher. Any entities below 3NF should be justified. The granularity of the entity instance should be clearly defined. The PK is clearly defined and supports the selected data granularity. Attributes: Should be simple and single-valued (atomic data) Should document default values, constraints, synonyms, and aliases Derived attributes should be clearly identified and include source(s) Should not be redundant unless they are justified for transaction accuracy, performance, or maintaining a history Nonkey attributes must be fully dependent on the PK attribute Relationships: Should clearly identify relationship participants Should clearly define participation, connectivity, and document cardinality ER Model: Should be validated against expected processes: inserts, updates, and deletes Should evaluate where, when, and how to maintain a history Should not contain redundant relationships except as required (see Attributes) Should minimize data redundancy to ensure single-place updates Should conform to the minimal data rule: “All that is needed is there and all that is there is needed.” Data Modeling Checklist Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. cengage.com/mindtap Fit your coursework into your hectic life. Make the most of your time by learning your way. Access the resources you need to succeed wherever, whenever. • �Get�more�from�your�time�online�with�an�easy-to-follow� five-step�learning�path. • �Stay�focused�with�an�all-in-one-place,�integrated� presentation�of�course�content. • �Get�the�free�MindTap�Mobile�App�and�learn� wherever you are. Break limitations. Create your own�potential,�and�be�unstoppable� with�MindTap. MINDTAP. POWERED BY YOU. Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. DATABASE SYSTEMS Carlos Coronel | Steven Morris Design, Implementation, and Management 13e Australia • Brazil • Mexico • Singapore • United Kingdom • United States Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword for materials in your areas of interest. Important Notice: Media content referenced within the product description or the product text may not be available in the eBook version. Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 © 2019, 2015 Cengage Learning, Inc. ALL RIGHTS RESERVED. No part of this work covered by the copyright herein may be reproduced or distributed in any form or by any means, except as permitted by U.S. copyright law, without the prior written permission of the copyright owner. For product information and technology assistance, contact us at Cengage Learning Customer & Sales Support, 1-800-354-9706 For permission to use material from this text or product, submit all requests online at www.cengage.com/permissions Further permissions questions can be emailed to
[email protected] Screenshots for this book were created using Microsoft Access®, Excel®, and Visio® and were used with permission from Microsoft. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Oracle is a registered trademark, and Oracle12 c and MySQL are trademarks of Oracle Corporation. iPhone, iPad, iTunes, and iPod are registered trademarks of Apple Inc. Library of Congress Control Number: 2015955694 Student Edition ISBN: 978-1-337-62790-0 Loose Leaf Edition ISBN: 978-1-337-68882-6 Cengage 20 Channel Center Street Boston, MA 02210 USA Cengage Learning is a leading provider of customized learning solutions with employees residing in nearly 40 different countries and sales in more than 125 countries around the world. Find your local representative at www.cengage.com. Cengage Learning products are represented in Canada by Nelson Education, Ltd. To learn more about Cengage, visit www.cengage.com Purchase any of our products at your local college store or at our preferred online store www.cengagebrain.com. Database Systems: Design, Implementation, and Management, 13th Edition