ISM 641 UAGC Wk 4 Creating a Database in Microsoft SQL Server Management Studio

User Generated


Computer Science

ISM 641

The University of Arizona Global Campus UACG



Prior to beginning work on this assignment read Chapter 9 of your textbook, review the Microsoft Create a Database website and review any relevant information in this week’s lecture. Review the chapter readings and the website to learn more about the process of creating tables and related attributes.

This assignment will be completed within the Virtual Lab Environment. For this assignment, you will access your virtual Structured Query Language (SQL) server, create a database and translate the normalized ERD from the Week Three assignment into the SQL server entities. Within the SQL server database all the following requirements must be met.

  • Create all entities with appropriate attributes
  • Select appropriate data types for all fields
  • Translate business requirements into integrity constraints
  • Build the ERD using the SQL Server Diagramming tool

Once you have created your SQL server database you will create a document. Take screenshots of the ERD and each of the individual tables within the database and paste these with appropriate labels into the document. You must submit the document via Waypoint for grading. Your instructor will also review your SQL server database within your virtual lab for grading purposes.

The assignment

  • Must be six to eight screenshots in length and formatted according to APA style as outlined in the Ashford Writing Center.
  • Must include a separate title page with the following:
    • Title of paper
    • Student’s name
    • Course name and number
    • Instructor’s name
    • Date submitted

Unformatted Attachment Preview

1 The Normalization Process: Converting the ERD Audrey Lane-Kinney The University of Arizona Global Campus ISM 641: Database Design and Management Robert Rossi April 26, 2021 2 The Normalization Process: Converting the ERD Normalization is a multistep process that is executed on data models to attain optimal database design. When a database is at the minimum (first normal form), the user is determined to normalize it to the 3rd normal form. Ideally, the urge to normalize the database further is still there, which requires normalizing to the Boyce-Codd Normal Form. The utilization of the fourth and fifth normal forms is far less common despite their existence. Therefore, normalization refers to the progression of proficiently organizing data in a database. The process comprises multiple steps known as forms, and the array of forms commences from 1NF to 5NF. The normalization process is essential in diminishing intricacy and redundancy as it critiques the novel data sorts utilized in a table. It is beneficial to split the larger databases table into smaller tables and connect them utilizing relationships. The process circumvents repeating groups as well as duplicates data in a table. Nevertheless, normalization has several downsides in the count of generating a longer task. Since there are more tables to link, the necessity to connect the various tables intensifies, and the task becomes more monotonous (longer and slower). The database becomes harder to realize as well. First Normal Form (1NF) Every table holds the comprehensive nuclear data elements, no duplicate data, and an allotted primary key (no duplicate groups) in this phase. A table in the first normal form contains no duplicated rows and atomic, meaning no duplicate arrays or groups, and each cell is valued. The first normal form tables have the same types of entries in the columns, and each row comprises analogous columns. Besides, the order of the columns or rows is less significant, and the table has a primary key because it contains no duplicated rows. 3 For instance, in the product table, the product modules and the product details must be keyed in distinct fields and not parted by commas. The modules or classes of data must be shifted to a new table with a link back to the predominant table, commonly referred to as the foreign key. Product Table Product ID Product Names 1 Mac-book air 2 HP Company Name Apple Inc Company Location California JPMorgan Chase & New York Co. Taken Modules Table Product ID Supply ID Modules 1 1 Introduction to PCs 1 2 Firewall Architecture Second Normal Form (2NF) In the 2NF step, the redundant data is disregarded. Therefore, if an attribute depends on only part of a multivalued key, it must be moved to a separate table during normalization. A table to be in 2NF should meet the entire database requirements surrounding the first normal 4 form. If every single non-key trait is entirely functionally reliant on the entire primary key data, that does not rely on the table's primary key directly. Then, in such a scenario, the attribute must be shifted into another table. A user has to formulate if the primary key imposes every single non-key attribute. It is not possible to determine the attribute's values with any subset of the primary key. In the second normal form, a table should have the comprehensive nuclear data attributes, a designated primary key to eliminate chances of duplicate rows and have no repeating groups. Also, every table should have entire non-primary key traits copiously reliant on the unabridged primary key. Supplier Table Supplier ID 1 Supplier Names John Kellick Corporate Name Apple Inc Corporate Location California JPMorgan Chase & 2 Laura Grey New York Co. Modules Table Supplier ID Module 1 Introduction to PCs 2 Firewall Architecture 5 Third Normal Form (3NF) Suppose a table meets all the database requirements for 1NF, 2NF and is without transitive dependencies. In that case, it is in 3NF, i.e., each column is directly dependent on the primary key (PK), and all attributes not dependent on the PK must be eliminated. It is aimed at reducing redundancies and maximizing the data integrity. 3NF meets the following criteria: 1. Each table stores all-atomic data items without repeating groups and with a designated primary key. 2. Each table has all non-primary key attributes whose functionality is fully dependent on the whole primary key. 3. The tables do not have transitive dependencies. EXAMINATIONS Score ID Student number H12/BSC/16 2745 Course Code Marks Course Name Total Marks BSC/202 75 BSC IT 75 From this table, the course name is dependent on the course code and the student number. Additionally, the total score is dependent on the course name. With the primary key in this table being Student_ID + Subject_ID, all attributes are dependent on it except for the total score; 6 transitive dependency. Therefore, to arrive at a 3NF, this transitive dependency must be removed. SCORES Score ID H12/BSC/16 Student Number Course Code 2745 BSC/202 EXAMS Course Name Total_marks BSC IT 75 Fourth Normal Form (4NF) This is aimed at removing multivalued dependencies, i.e., a table with independent attributes, but they both depend on another common attribute in the same table. To achieve this, all multiple independent relationships are isolated such that no table has multiple indirect 1:M or M: M relationships. In addition to this, no relation should have more than a single multivalued attribute (no non-trivial multivalued dependencies exist in any table). 4NF holds that despite a database satisfying the Boyce-Codd Normal Form (BCNF), it should exhibit no more than a single multivalued dependency. To get a 4NF from BCNF, get rid of all independently multivalued components and put them into two new ‘parent’ entities. For example, consider an employee with many skills and 7 several dependents. The skill and the dependent information repeat (are redundant) and independent of one another. Therefore, move them to separate tables. Fifth Normal Form (5NF) A table in 5NF minimizes redundancy by isolating all multiple relationships that are semantically related. For any table in the 5NF, all its non-trivial join dependencies are hint by candidate keys. However, some practical constraints may exist on information to justify the separation of logically related M: M relationships. For any table to be in the 3NF, it must satisfy these criteria: atomic data items, no transitive dependency, and all attributes to be depended on the Primary Key. For instance, the assignment table in the original ERD had violated the 3NF rules in that it was refencing two 8 distinct tables to acquire data, the products table and the employees’ table. This creates a functional dependency and thus the need to create a junction table (Assignee) to link both the Employees and the Products tables. Additionally, a to avoid redundant license IDs, the need for an individual licenses table arises. Moreover, in each table, the functionality of the attributes is depended on the table’s primary key. This new ERD also meets the 1NF since each table holds non-duplicated nuclear data elements. i.e., no attribute contains multiple values from each domain. This was achieved by breaking down the products table into two, purchases and products. The possibility of having two closely related columns in the same table is hereby eliminated. Any table in 2NF ought to be in 1NF and without non-prime attributes whose functionality is dependent on any candidate key in the relation. From our translated ERD, all tables conform to this. They all have atomic values as well as without non-prime attribute which depend on proper subsets of candidate keys. In conclusion, database normalization is a vital process in the design of DBMS as it forms the basis of redundancy removal, which will, in turn, come in handy in ensuring the efficiency and accuracy of the database. It, therefore, in our best interest to ensure normalization is undertaken to the very last step for better functionality of the DBMS. 9 References Eessaar, E. (2016). The database normalization theory and the theory of normalized systems: finding common ground. Baltic Journal of Modern Computing, 4(1), 5. Lum, V., Dadam, P., Erbe, R., Günauer, J., Pistor, P., Walch, G., ... & Woodfill, J. (1987). Design of an integrated DBMS to support advanced applications. In Foundations of Data Organization (pp. 31-49). Springer, Boston, MA. Database Design and Management ISM641 ISM641 - Introduction Week 2 Lab Week 4 Lab ISM641 - Introduction Access your virtual lab and use Visio to create an entity-relationship diagram (ERD) from the entities and attributes listed for the ConstructCo Database Project. Your ERD must include the following. Identify all entities and attributes Define all relationships between entities (including proper relation semantics) Select the appropriate data types for each of the attributes Identify your primary keys Once you have created your Visio, save it as a PDF and download to your computer from Practice Labs. Attach your ERD PDF to your initial post. Lab Diagram During your session you will have access to the following lab configuration. Depending on the exercises you may or may not use all of the devices, but they are shown here in the layout to get an overall understanding of the topology of the lab. Connecting to your lab In this module you will be working on the following equipment to carry out the steps defined in each exercise. PLABAPP01 Each exercise will detail which device you are required to work on to carry out the steps. Copyright Notice This document and its content is copyright of Practice-IT - © Practice-IT 2015. All rights reserved. Any redistribution or reproduction of part or all of the contents in any form is prohibited other than the following: 1. You may print or download to a local hard disk extracts for your personal and non-commercial use only. 2. You may copy the content to individual third parties for their personal use, but only if you acknowledge the website as the source of the material. You may not, except with our express written permission, distribute or commercially exploit the content. Nor may you transmit it or store it in any other website or other form of electronic retrieval system. Week 2 Lab Week 2 Lab Creating an ERD using Visio Once the Windows desktop appears in the RIGHT pane of your screen, click on Start -> All Programs ->Microsoft Office -> Visio 2010) or If you have an icon on your desktop, Double CLICK on the Visio Icon. Open a new project and look for the Templates. Click on the Software and Database Template. Now click on the Database Model Diagram. Now Drag and Drop the object you need to create your diagram on your template. Once you complete your diagram, save and submit. Week 4 Lab Switch on PLABAPP01. Wait until the desktop appears on the right. If the connection does not establish, refresh the connection. The Windows desktop will appear on the right. Now click on START -> All programs -> Microsoft SQL Server 2008 R2, and select SQL Server Management Studio. The SQL Server 2008 R2 login in screen will be displayed Click on Server Name and Double Click in Browse for more from the drop down Now click on the + sign next to Database engine Select PLABAPP01\SQLSERVER as shown below and then Click OK Click Connect (at the bottom of the screen). You have now connected to the SQL engine. You should be able to create your database at this stage from the screen below. Click on Database and right click (Make sure it is RIGHT CLICK) select new database The new database creation window will appear and give your database a name (Grade_Report). Type Grade_Report. Click OK Your Database (Grade_Report) will appear at the bottom of the list of databases on the left. If you do not see Grade_Report. Double Click on Databases to refresh. Click on the + next to Grade_Report The list will expand to the following: Right click on Tables and select create new table Change the name of the table to Report otherwise the system will default the name to table1. See below where to change the name In the Column name type the name Student_ID and in the Data Type Column use NChar (10) Enter the following column names after Student_ID and use as shown in the picture below with the Data Types. Unchecked all the Allow Nulls (we do not want empty fields). See your module 3 assignment for more details. Now, we are going to make the StudentID as the primary key. Click on the StudentID field -> Table Designer (top menu bar) -> Set Primary Key Click on the DISKETTE (2nd menu bar in blue) to save the table. Now you are ready to write a query to insert data in the table. Click on the QUERY TAB Type in the following query, or cut and paste from SQL.wk4.txt located on the Practice Labs intranet. Open Internet Explorer in your Lab’s desktop and go here: http://intranet/ Select the Public Files tab, then Data Files/AU/ISM641 USE REPORT_GRADE; GO INSERT INTO dbo.name_of_your_table(StudentID, StudentName, Phone, DOB, Address, Email_address, Course_Number, CourseName, Grade, Department_ID, Dept_Name) VALUES (‘121212’, ‘TOM JONES’, ‘2134567898’, 10/11/1997, ‘’,’2134’, ‘Algebra’, ‘A’, ‘222’); INSERT INTO dbo.name_of_your_table(StudentID, StudentName, Phone, DOB, Address, Email_address, Course_Number, CourseName, Grade, Department_ID, Dept_Name) VALUES (‘121212’, ‘TIM JONES’, ‘2134567898’, 10/11/1997, ‘’,’2134’, ‘Algebra’, ‘A’, ‘222’); INSERT INTO dbo.name_of_your_table(StudentID, StudentName, Phone, DOB, Address, Email_address, Course_Number, CourseName, Grade, Department_ID, Dept_Name) VALUES (‘121212’, ‘ELIZ JONES’, ‘2134567898’, 10/11/1997, ‘’,’2134’, ‘Algebra’, ‘A’, ‘222’); INSERT INTO dbo.name_of_your_table(StudentID, StudentName, Phone, DOB, Address, Email_address, Course_Number, CourseName, Grade, Department_ID, Dept_Name) VALUES (‘121212’, ‘TOM SMITH’, ‘2134567898’, 10/11/1997, ‘’,’2134’, ‘Algebra’, ‘A’, ‘222’); INSERT INTO dbo.name_of_your_table(StudentID, StudentName, Phone, DOB, Address, Email_address, Course_Number, CourseName, Grade, Department_ID, Dept_Name) VALUES (‘121212’, ‘TODD JONES’, ‘2134567898’, 10/11/1997, ‘’,’2134’, ‘Algebra’, ‘A’, ‘222’); HINT: CREATE 1 and execute it and cut and paste until you have at least 5 with different names Now you are ready to execute the query to populate the table with data. Click on Execute Take a screen shot of your query Normalize the DB and create the Diagram. Once you have Normalized the database using what you have learned in Week 1 to Week 4, we are going to create a diagram. Here, we are going to do some “Prep work” first to change ownership of the database. Right click on Database Diagram right below Grade_Report and select Create New Database Diagram See below for the screen after the right click. At this stage, you will get the WARNING MESSAGE BELOW. As directed, we’ll change the ownership of the database. Right Click on Grade_Report and Select Properties. Now that you are in the Grade_Report Property page, double click on Files (1) and the … (2) as shown on the screen below. These steps will allow us to change the Owner of the database. In the box Enter the object name to select [examples], type sa and click on the button Check Names. Square brackets will be added around sa. Click OK twice for both Windows to close. Now you have change the owner of the database to sa. You are ready to create your diagram. Right Click on Database Diagram as you did before. Select new Database Diagram and a list of your tables will be displayed. Select the table you want on the diagram from the list and click on ADD at the bottom. The tables will show in a new windows and now you can DRAG and DROP the fields you want to “link” And when you are ready to save your diagram, right click on the name on the menu as shown below and chose Save Diagram1. Give your diagram a new name as shown below and click OK ISM641 Database Design Project: Business Rules Business rules for database design • The design must track the date the workstation was assigned and whether it is a desktop PC or a laptop. • The design must track the date a software was installed on a given workstation and the type of license that was used. • Only one workstation can be assigned to an employee. • The design must track every workstation/Laptop (location and assignee). • The design must track employees and capture the following information (EmployeeID, Firstname, Lastname, title, Date of Hire, Salary, and department). o The title would be used to determine whether the employee is an associate, lead or manager. o Values for the department attribute would include accounting, research, QA , operations , HR and administration. • The design must have the ability to track workstations need and the following information captured (WorkstationID, type, location, date of purchase). • The design must track the PC/laptop configurations (memory, disk space and CPU speed). • The design must have the ability to track when a given workstation was assigned to a given employee. o • This date is usually different from the purchase date. The design must track the type of licenses used (named license, volume license, concurrent licenses). o Named user license: A license that is assigned and used by a particular user o Volume license: Licenses that can be installed on any number of machines but can be used on a limited number on machines at any given point depending on the purchased volume o Concurrent Licenses: Licenses that can be installed on any number of machines but can be used on a limited number of machines at any given point • The design must have the ability to track expiration dates on existing licenses.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.


Creating a Database in Microsoft SQL Server Management Studio

Audrey Lane-Kinney
The University of Arizona Global Campus
ISM 641: Database Design and Management
Robert Rossi
May 3, 2021


Creating a Database in Microsoft SQL Server Management Studio

Great! Studypool always delivers quality work.


Related Tags