Structured Query Language (SQL) - OR - Use MS Access

User Generated

QrLbhat

Computer Science

Description

Week 7 Lab 4 Submission

Lab 4: Structured Query Language (SQL) - OR - Use MS Access and explain how you would perform the required steps below.

Due Week 7 and worth 75 points

Refer to the database design crested in Lab 2: Modifying a Database Design to complete this Lab.

Perform the following steps in MySQL or MS Access:

  1. Create the tables and relationships from the database design discussed in Lab 2.
  2. Add at least five (5) records into each table (Note: You must determine the field values).
  3. Create a query with all fields from the student table, where the student's last name is "Smith."
  4. Create a query that includes students’ first names, last names, and phone numbers.
  5. Create a query that includes instructors' first names, last names, and courses they teach.

Include the following screen shots in a Microsoft Word document to show the completion of the steps above:

  • Screen shot depicting the tables created within MySQL or MS Access.
  • Screen shots depicting the records added into each table.
  • Screen shots depicting the SQL code and the query results from each of the queries created.

Your assignment must follow these formatting requirements:

  • Submit the lab as a Microsoft Word document.
  • Include a cover page containing the title of the assignment, the student’s name, the professor’s name, the course title, and the date. The cover page is not included in the required assignment page length.

The specific course learning outcomes associated with this assignment are:

  • Prepare database design documents using the data definition, data manipulation, and data control language components of the SQL language.

Unformatted Attachment Preview

Running head: MS Access Setup 1 Topic: MS Access Setup Timothy DeYoung Dr. Edwin Otto and Professor Nick George CIS 111 May 4, 2018 MS Access Setup 2 In this Lab the one that I downloaded is the one that I did not have yet to complete the set. The one is Microsoft Access. I did the research on MySQL but, right now I really just don’t know a lot about it nor have I heard a lot about it either especially with me not working right now. With downloading MS Access, I had some problems due to the Microsoft program that I had was just a trail with a free download that I got last semester for a project and did not have everything. And now thanks to this lab I have Microsoft Office with everything. But, other than that little problem that was it everything else went good with the download. Do to not really knowing what it is and never using MySQL. I did some research on MySQL and found some main capabilities of MySQL. Some of the main capabilities are MySQL are great for web-sites and social media because as the web-site and social media continue to grow to be able to hold the growing amount of data so is MySQL, so it can continue to be the best choice out there. MySQL has also very reliability and easy maintainability that means database administrators don't waste time troubleshooting performance or have any downtime issues with MySQL and be able to give them time to work on something else. MySQL is also great for using the cloud as well. MySQL’s performance, reliability and ease of use make it the perfect choice for deployments both in public and private clouds with saving and retrieving information. Also, in my research I found that MySQL has a MySQL Workbench as well. The Workbench is a visual tool that enables developers, and data architects to design, develop and administer MySQL servers. The MySQL Workbench also allows you to design because it includes everything you need for creating complex models to engineering. Also develop as well because it has everything for optimizing, creating, and executing queries. With the Database Connections Panel, it enables developers to easily manage the database connections. and objects. I think if I knew more about MySQL I think that I MS Access Setup 3 would use it a lot. With the database design I think I would be able to change it over to MySQL program because reading about the Workbench it sounds like it has all of the tools that you would need for the database design built in to the program. MS Access Setup 4 References Datamation https://www.datamation.com/storage/8-major-advantages-of-using-mysql.html 8 Major Advantages of Using MySQL - Datamation https://www.datamation.com/storage/8-major-advantages-of-using-mysql.html Percona Delivers First, Free, Open Source Method of Centralized Key Management for MySQL General Availability of Percona Server for MySQL Also Provides Additional Encryption Capabilities 1 DATABASE DESIGN Database Design Timothy DeYoung Dr. Edwin Otto and Professor Nick George CIS 111 April 27, 2018 DATABASE DESIGN Section 1: Modified Diagram 2 DATABASE DESIGN 3 Section 2: Design Summary Design Summary Database design comprises of a sequence of events that enable the designing, development and deployment of a database. A good database design makes the deployment and performance of the database efficient. Thus, this process helps ensure that database systems meet the requirements specified by the users (Datanamic, n.d.). In this case, the design process seeks to achieve the physical and logical models for the proposed database system. I believe the diagram is a representation of the expected database design because the process is largely done based on the principles of database design. Specifically, the first process involved breaking the data into small logical pieces based on the scenario provided by the requirements analysis activity. The next step after listing the data was the identification of entities. Entities comprise of information stored in the database. In this context, I managed to identify STUDENT, COURSE, INSTRUCTOR and PERFORMANCE as the entities. This was then followed by the identification of attributes for each entity. Attributes describe entities, for example, the STUDENT entity type has Student_ID as an attribute. Further, the primary and foreign keys were selected for the various entities. Primary keys are unique identifiers for an entity (Datanamic, n.d.). For instance, the Student_ID and Course_ID are primary keys for the entities STUDENT and COURSE respectively. Also, the Student_ID and Course_ID are foreign keys for the entity PERFORMANCE. The next step involves establishing the relationships that exist between the different entities. This step explores how one entity relates to the other. For example, students enroll for a certain course; an instructor teaches certain courses and students obtain grades for the various 4 DATABASE DESIGN courses. In this step, the cardinalities of the relationships are developed. Cardinalities show how much of one entity belongs to the other entity (Datanamic, n.d.). For example, an instructor can teach multiple courses, a student can take multiple courses, and a course can be taught by many multiple students. The last step was developing the model using a modelling software based on the rough draft implemented through the subsequent stages. As such, I believe the diagram captures all the aspects of the database design. Assumptions Organizations use data in diverse ways to conduct business. Business rules determine how the data in an organization is utilized (Coronel, Morris, & Rob, 2011). In this case, the following assumptions were made when generating the business rules and in coming up with the database design: a student can learn more than one course, and an instructor can lecture in more than one course. 5 DATABASE DESIGN References Coronel, C., Morris, S., & Rob, P. (2011). Database systems: Design, implementation, and management. Australia: Course Technology Cengage Learning. Datanamic. (n.d.). Introduction to Database Design. Retrieved April 27, 2018, from http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html Running head: Creating a Database Design 1 Topic: Creating a Database Design Timothy DeYoung Dr. Edwin Otto and Professor Nick George CIS 111 April 21, 2018 Creating a Database Design 2 This database would mainly be used to store student’s information in a college. At any time, it should be able to accept new data and display information when called upon at any time. For example, it must be able to give information on when a student joined the college, what course he did and when the student left the college. The entities used to develop the database are Instructor, students and course and personal information. Let us consider the attributes of the entities: Students The entity STUDENTS have the attributes StudentID, Name, DOB, SSN, Street, City, state and Pin. These attributes can be classified into two types; the direct characteristics of the entity such as the StudentID or the new relationship of the entities such as the grade and over all GPA that is an attribute that relates to the student, the course or courses, and the instructor. To make the database presentable and manageable, the database associates each grade and GPA to a certain semester and year it was given. This necessitates the creation of other attributes, year, semester, and grade or grades if more than one class. Consequently, the attribute grade gets other attributes StudentID, CourseID, and InstructorID from the entities Students, Course, and Instructor respectively. These attributes are used as foreign keys. The attribute StudentID is used as the primary key since it distinguishes one student from the other. The degree to which I think a Visio diagram should reflects the database design. Essentially, Microsoft Visio diagrams are used for illustrating concepts diagrammatically. This is because it simplifies the design of an otherwise complex system so that people can understand it. In these case, I am confident from doing reading on Visio (100% sure) that the Visio diagram Creating a Database Design 3 fully reflects the database design. This is because, before designing the Visio diagram, I carefully planned the design of the database. Entities were sought out from the database. After this, I opened Mas Visio program and navigated to business stencils. In the business stencil, there are built in templates that you can use but I decided to build the database from scratch. The entities were first brought into focus and the relationship between them. Since database design is about entities, attributes and the relationships. I think I understand and was able to do the design good to the best of my understanding. Creating a Database Design 4 Students PK Advising StudentID Advises Name Street Area City State Pin StudentID InstructorID Advisor Graded Grades StudentID InstructorID CourseID Semester Year Grades Instructor Grader PK InstructorID InstructorName InstructorDeptt InstructorCourse Subject Course CourseID CourseName CourseDuration CourseCapacity CourseCredit Creating a Database Design 5 Database Illustration
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

Hey buddy, herein find...


Anonymous
Really helpful material, saved me a great deal of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags