Transcription
M I C R O S O F T T E C H N O L O G Y A S S O C I AT EStudent Study GuideEXAM 98-364Database Administration Fundamentals
Preparing forfor MTA CCertCertifiertificcacationationnMICROSOFT TECHNOLOGYTECECHNOLOGYCHNOLOGY AASSOCIATESSOCIATE ((MTA)(MTAMTAA)STUDENT STUDYUDY GUIDE FORFDEVELOPERSDEVELOPERS98-364Database AdministrationFundamentals
AuthorsPeggy Fisher (Web Development and Database Administration).Peggy teaches computer science at a rural high school in central,Pennsylvania. Indian Valley High School offers courses inprogramming (C#, VB, and Java for the AP course), and Web design(Expression Web, HTML, JavaScript, and CSS). Peggy worked for alarge insurance company outside Philadelphia, Pennsylvania, priorto leaving the corporate world to join the field of education. She hasbeen at IVHS for the past eight years and truly enjoys her new career.Peggy also teaches part-time at Pennsylvania State University in theContinuing Education program. Her goal in teaching is to instill thelove of learning so that her students will graduate and become lifelong learners. Peggy is the co-author of the Web Development ExamReview Kit in the MTA Exam Review Kit series.Patricia Phillips (Lead Author and Project Manager). Patricia taughtcomputer science for 20 years in Janesville, Wisconsin. She servedon Microsoft’s National K-12 Faculty Advisory Board and editedthe Microsoft MainFunction website for technology teachers fortwo years. For the past five years she has worked with Microsoftin a variety of roles related to K-12 curriculum development andpilot programs including Expression Studio web design and XNAgame development. In her role as an author and editor, Patriciawrote several articles and a student workbook on topics includingcomputer science, web design, and computational thinking. She iscurrently the editor of the Computer Science Teachers Associationnewsletter, the Voice.This content is only for use by or provision to students for their personal use.Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or shouldbe inferred.Microsoft and other trademarks listed at ctualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. 2010 Microsoft Corporation. All Rights Reserved. This content is provided “as-is” and Microsoft makes no warranties, express or implied.
ContentsIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vCareer Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viExploring Job Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viiiValue of Certification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x98-364CHAPTER 1CHAPTER 2DATABASE ADMINISTRATION FUNDAMENTALSUnderstanding Core Database Concepts . . . . . . . . . . . . . . . . . . . . . . . 31.1Understand how data is stored in tables . . . . . . . . . . . . . . . . . . . . . . . 51.2Understand relational database concepts . . . . . . . . . . . . . . . . . . . . . . 71.3Understand data manipulation language (DML) . . . . . . . . . . . . . . . . 91.4Understand data definition language (DDL) . . . . . . . . . . . . . . . . . . . 11Creating Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132.1Choose data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152.2Understand tables and how to create them . . . . . . . . . . . . . . . . . . . 172.3ACreate views using T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192.3BCreate views using a graphical designer . . . . . . . . . . . . . . . . . . . . . . 212.4Create stored procedures and functions . . . . . . . . . . . . . . . . . . . . . . 23
CHAPTER 3CHAPTER 4CHAPTER 5CHAPTER 6Manipulating Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253.1ASelect data utilizing SELECT with simple queries . . . . . . . . . . . . . . . 273.1BSelect data utilizing SELECT with complex queries . . . . . . . . . . . . . . 293.2Insert data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313.3Update data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333.4Delete data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Understanding Data Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374.1Understand normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394.2Understand primary, foreign, and composite keys . . . . . . . . . . . . . 414.3Understand indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Administering a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455.1Understand database security concepts . . . . . . . . . . . . . . . . . . . . . . 475.2Understand database backup and restore . . . . . . . . . . . . . . . . . . . . 49Database Concepts Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Database administration fundamentals review . . . . . . . . . . . . . . . . . 53ivContents
IntroductionMTA validates building-block technology conceptsand helps students explore, discover andpursue successful careers in Information Technology(IT) in an exciting and rewarding way! As the first stepin the Microsoft Technology Certification Series, thisnew, entry-level certification provides students withconfidence, credibility, and differentiation.Explore IT career options without committinga lot of time and resources MTA exams validatethe core technology knowledge that is in demandtoday by businesses around the world. Whether youwant to explore becoming a network administrator,software engineer, web developer, or databaseanalyst, MTA gets you started on the right path.Prepare to compete A little investment in IT cango a long way in today’s job market. Becoming MTAcertified helps you build a solid foundation to preparefor intermediate technology studies and for MicrosoftCertified Technology Specialist (MCTS) certifications.It can also help you compete on college admissionsand jumpstart your IT career planning!Empower yourself As the first step towardbecoming an MCTS, MTA shows your commitment totechnology while connecting you with a communityof more than five million Microsoft CertifiedProfessionals. Learn from them and show them whatyou know by becoming MTA certified!This MTA Student Study Guide serves as a study toolto help students prepare for their MTA certificationexam. Students are challenged with real-life situationsfor each of the major topics covered in the exam.Although successful completion of the study guideexercises does not guarantee that you will pass yourMTA exam, it is an excellent way to gauge yourreadiness to take the exam and build confidencethat you know your stuff on exam day.I wish you all the best as you prepare for a successfulcareer in technology!Victoria PohtoVictoria PohtoMTA Product Marketing ManagerIntroductionv
Career PlanningMost IT solutions or infrastructure built onMicrosoft technologies require proficiencywith one or all of the following products, oftenreferred to as “The Microsoft Stack.” Microsoft Windows Server as the data centeror development platform Microsoft SQL Server as the data and businessintelligence (BI) platform Microsoft Visual Studio as the suite of applicationlife-cycle management toolsMTA is the starting point of Microsoft technologycertifications, providing aspiring technologists withthe fundamental knowledge essential to succeedwith continued studies and a successful career withtechnology.Preparing for and becoming MTA certified helps youexplore a variety of career paths in technology without investing a lot of time and money in a specializedcareer path. When you find a path that is right foryou, Microsoft learning products and certification canhelp you prepare and guide your longer-term careerplanning.If you already know that you want to start buildinga career in technology, MTA preparation and certification is the recommended entry point. BecomingMTA certified shows that you have a firm workingviCareer Planningknowledge of the fundamental IT concepts criticalfor success with intermediate learning andcertifications such as Microsoft Certified TechnologySpecialist (MCTS). Moreover, Microsoft certificationsdemonstrate an individual’s commitment of selfinvestment and confidence to take his or herknowledge and skills to the next level with anindustry-recognized credential.MTA is not a “career certification,” meaning thatemployers recognize you as “job ready,” but it is thefirst step toward that career goal and can helpdifferentiate you for an internship or to collegeadmissions committees. As you prepare for your firstjob focusing on technology, be sure that youare equipped with an MCTS credential—theintermediate level certification that validatesMicrosoft product and technology skills.The MTA Certification path on the next page showsyou the MTA exams that are recommended priorto taking on some of Microsoft’s intermediatetechnology certification, MCTS.
Career Planningvii
Exploring Job RolesChoosing a career path is a big decision and it’snot always easy, but you’re not alone! Microsoftcreated a career site to help students understand theoptions and possibilities of pursuing a career in IT.The site also connects you with learning resources,student techie communities, and much more to helpyou prepare for a career in technology.viiiServer AdministratorTo chart your career with Microsoft technology,visit -charts.aspx.As a server administrator, you are in chargeof implementing and managing someof the most important technology in yourorganization—the servers. You use extensivemonitoring and profiling tools to manage thenetwork and tune systems so they perform at optimallevels. You are an expert in Active Directory , and youhave an in-depth understanding of networkprotocols, and file and directory security.Database AdministratorComputer Support TechnicianAs a database administrator, you are in chargeof important databases that span multipleplatforms and environments. You are a strongteam player who thrives in a fast-pacedenvironment. You build complex, highly scalabledatabases that meet business needs and securityrequirements. You are an expert in optimizing,maintaining, and troubleshooting databases, but alsoin designing archival, data distribution, and highavailability solutions.Consider starting your IT career by becominga consumer support technician. You don’tneed any formal work experience, but acompany might require that you know how toinstall, administer, and troubleshoot operatingsystems in a home network environment that hasdesktop computers, laptops, and printers. As aconsumer support technician, you’ll also handlenetwork, virus, malicious software, and hardwaresupport issues. You’ll typically find this position insmall to medium-sized organizations.Exploring Job Roles
Exploring Job RolesWeb DeveloperAs a web developer, you are an expert inusing the dynamic programming tools andlanguages that fuel the web. You might workindependently or be part of a team that buildsand integrates interactive web sites, applications, andservices for both internal and public sites. Your role isto make it work, which means developing webapplications and testing them on various browsers,enhancing and modifying them as necessary toensure the best experience for the user. As a webdeveloper, you might also architect websites, designdata-driven applications, and find efficient clientserver solutions. You must have an in-depth understanding of the software development life cycle andbe able to communicate project status, issues, andresolutions.Windows DeveloperAs a Windows client developer, knowing howto optimize Windows code and track bugs is agiven. But you also know how to use MicrosoftVisual Studio and the Microsoft .NET framework to design, develop, test, and deploy Windowsbased applications that run on both corporate serversand desktop computers. Your key talents includeunderstanding multiple Windows application modelsand n-tier applications, and knowing how to workwith object-oriented programming, algorithms, datastructures, and multithreading. Windows developershave an in-depth understanding of software engineering principles, software life cycles, and securityprinciples.Additional Online Resources for New //msdn.microsoft.com/rampupImagine CupThe Imagine Cup is theworld’s premier studenttechnology competitionwhere students fromaround the world can learn new skills, make newfriends, and change the world. Competitionsinclude Software Design, Embedded Development,Game Design, Digital Media and Windows Phone 7.The brightest young minds harness the power oftechnology to take on the world’s toughest problems.www.imaginecup.comExploring Job Rolesix
Value of CertificationTechnology plays a role in virtually everythingwe do. In the 20-plus years since Microsoft hasbeen certifying people on its products and technologies, millions of people have gained the knowledge,expertise, and credentials to enhance their careers,optimize business solutions, and create innovationwithin just about every business and social sectorimaginable. Today’s Information Technology (IT)hiring managers are more often using professionalcredentials, such as Microsoft certification, to identifyproperly skilled IT candidates. Certification becomesa way to easily differentiate qualified candidates in asea of resumes.The job outlook for IT professionals, as reported ina study prepared by the U.S. Department of Labor’sBureau of Labor Statistics (BLS), is positive! The BLSindicates an increase that will be “faster than theaverage for all occupations through 2014” forComputer Support Specialists, Systems Engineers,Database Administrators, and Computer SoftwareEngineers. One significant message resulting fromthis study is that information and communicationsxValue of Cer tificationtechnology (ICT) skills are the entry ticket to thejob market, regardless of the country, industry, orjob function. Information Technology is clearly anarea worth investing time, resources, and educationin – and technology certification is a key part of theeducation process, validating product and technologyexpertise as a result of their learning experiences.Microsoft IT Certifications provide objective validationof the ability to perform critical IT functions successfully for worldwide IT professionals, developers, andinformation workers. Microsoft certifications represent a rich and varied spectrum of knowledge, jobroles, and responsibilities. Further, earning a specificcertification provides objective validation of thecandidate’s ability to perform critical IT functionssuccessfully. Embraced by industry professionalsworldwide, Microsoft certification remains one of themost effective ways to help reach long-term careergoals.
MTA 98-364DATABASEADMINISTRATIONFUNDAMENTALS
1UnderstandingCore DatabaseConceptsIN THI S C HAP TE R 1.1 Understand how data is stored in tables 1.2 Understand relational database concepts 1.3 Understand data manipulation language (DML) 1.4 Understand data definition language (DDL)
OBJECTIVEU N D E R S TA N D I N G C O R E DATA B A S E C O N C E P T S 1 . 1Understand how data is stored in tablesSCENARIO: High-school student Rajesh M. Patel works part-time in his dad’s cycle shop in Pune, India. Pune wasthe first city in India to have dedicated lanes for cycles.Raj’s dad currently keeps track of his inventory using pencil and paper. Ordering parts and accessories orcounting inventory takes several days. Raj is taking a database management class in school and he realizesthat his dad’s business could benefit greatly by implementing a database management system.Raj needs to review a few basic concepts before he starts, and has made of list of topics he knows will beimportant in the first steps of the project.1. Identify tables that Raj might create for the database.a. a parts table, a cycle table, and an accessories tableb. one table for all parts, cycles, and accessoriesc. a table for each part, each cycle, and each type of accessory (a helmet table, a biking glovetable, and so on)2. Identify the fields that Raj should use for column headings of the parts table.a. part number, part name, cycle number, and cycle nameb. part number and quantity soldc. part number, part name, quantity, color, and cycle identifier3. What is the name of the data located at the intersection of a row and column?a. fieldb. recordc. variableThink of a databasetable as an Excel spreadsheet that contains rows and columns.The data in one tablemay need to referencedata in another.Understand how data is stored in tables5
Answers1. Raj will need these tables:a. a parts table, a cycle table, and an accessories table2. Raj should use these fields for the column headings of the parts table:c. part number, part name, quantity, color, and cycle number3. The name of the data located at the intersection of a row and column is:a. fieldEssential details A field is a location in a record in which a particular type of data is stored. A column is an attribute for a row or record. A row is a record within a table. A table is a database object consisting of rows and columns. A record is a data structure that is a collection of fields (elements), each with its own name and type thatappear in a table as group of fields across one row.FAST TR ACK HELP QL.80).aspx spx/36CHAPTER 1: Understanding Core Database Concepts
OBJECTIVEU N D E R S TA N D I N G C O R E DATA B A S E C O N C E P T S 1 . 2Understand relational database conceptsSCENARIO: The next step for Raj in the design of the database to streamline his father’s cycle business is todetermine the most useful relationships between the tables. He knows that he needs to create a relationaldatabase because it will be important to conduct searches by using data in one table to find additional datain another table. Figuring out a design that best meets the needs of the cycle shop is a critical step. Whilereviewing his current business requirements, he realizes that he needs to add another table to include thesuppliers of the parts for the cycles. This new table requires Raj to update the parts table to includea foreign key to the supplier table.1. What is the relationship between the supplier table and the parts table?a. one-to-oneb. one-to-manyc. many-to-many2. What is a good foreign key for the relationship between the cycle table and the parts table?a. part numberb. cycle model numberc. part name3. What is a suggested primary key for the accessory table?a. accessory numberb. accessory namec. accessory modelThe primary keymust be a valuethat is unique toeach record in thetable.Understand relational database concepts7
Answers1. The relationship between the supplier table and the parts table is:b. one-to-many. Every supplier provides one-to-many parts.2. A good foreign key for the relationship between the cycle table and the parts table is:a. part number. Each cycle will have many unique parts.3. A primary key for the accessory table is:a. accessory number. Many accessories might have the same name and model.Essential details Most modern database management systems implement a relational model in which the data is organizedin relations (tables). A one-to-one relationship is an association
98-364 Database Administration Fundamentals. Peggy Fisher (Web Development and Database Administration). Peggy teaches computer science at a rural high school in central, Pennsylvania. Indian Valley High School offers courses in program
