GED 101 E Week 8 Database Basics PDF

Databases Kenneth Laudon, Jane Laudon. Management Information Systems: Managing the Digital Firm. 13th Edition. 2014. Pearson.  Database Basics A field is a basic entity or data element, such as the name of a book or the telephone number  A record is a complete set of all of the data (fie...

Databases Kenneth Laudon, Jane Laudon. Management Information Systems: Managing the Digital Firm. 13th Edition. 2014. Pearson.  Database Basics A field is a basic entity or data element, such as the name of a book or the telephone number  A record is a complete set of all of the data (fields) about one person, place, event, or idea.  A table is a collection of records. Every record in a table contains the same fields in the same order.  A database consists of one or more tables and the supporting objects used to get data into and out of the tables. Data vs Information Data is the raw material and consists of the table (or tables) that comprise a database. Information is the finished product i.e. data is converted to information by selecting (filtering) records, by sorting the records, or by summarizing data from multiple records.  Relational Database Data contained in a single page or sheet (not multiple) are called flat or non-relational data. A relational database management is one in which data are grouped into similar collections called tables, and the relationships between tables are formed by using a common field.  Keys  Keys consist of one or more attributes that determine other attributes ◦ Ensure that each row in a table is uniquely identifiable ◦ Establish relationships among tables and to ensure the integrity of the data  Primary key: attribute or combination of attributes that uniquely identifies a row i.e. makes each record in a table unique.  Foreign key: primary key of one table that has been placed into another table to create a common attribute i.e. is a field in one table that also is stored in a different table as a primary key. Table Example 7 Types of Keys – Foreign vs Primary © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. What does a simple relational database design look like? The diagram below is called a Logical Data Structure (LDS) – also known as an Entity-Relationship (ER) diagram.  Relationaldatabases are built up of tables related to each other in ‘one-to-many’ relationships, in that one of something relates to many of the other thing. The relationships above mean (it helps to read both ways): ◦ One faculty includes many students or many students belong to one faculty ◦ One subject is taken by many students or many students take one subject ◦ One student is given many marks or many marks are given to one student  How are the tables linked? Every table must include a primary key – this key must uniquely identify each individual record in its table. Student number would make a good primary key in the student table, because no two students will ever have the same student number. Where a table is linked to another table, the primary key from the table at the ‘one’ end of the relationship must be added to the table at the ‘many’ end of the relationship, where it is known as a foreign key. Example of Tables: Supplier & Part Database Example The Final Database Design with Sample Records  Introduction to SQL  SQL is relatively easy to learn ◦ Nonprocedural language with basic command vocabulary set of less than 100 words ◦ Differences in SQL dialects are minor  Fundamental types of data ◦ Character data ◦ Numeric data ◦ Date data  Basic SQL Structure Select coulmn_name, column_name From table_name Where column_name operator value Operators in Where Clause Refer to the following table in the questions that follow Table name: Employee Select Name, Salary From Employee  Basic SQL Examples 1. SELECT * FROM tableName in which the asterisk (*) indicates that all columns from the tableName table should be retrieved. SELECT * FROM EMPLOYEE 2. To retrieve only specific columns from a table, replace the asterisk (*) with a comma separated list of the column names. For example, to retrieve only the employee number and name for all the rows in the employee table, use the query SELECT NUMBER, NAME FROM EMPLOYEE 18 Basic SQL Queries 3. Select specific columns given a certain criteria. For example, retrieve only the employee’s name department and salary where the location is New Jersey. SELECT NAME, DEPARTMENT, SALARY FROM EMPLOYEE WHERE LOCATION = “New Jersey” 19  Basic SQL Queries  Therows in the result of a query can be sorted into ascending or descending order by using the optional ORDER BY clause. The basic form of a query with an ORDER BY clause is SELECT NUMBER, NAME, SALARY, LOCATION FROM EMPLOYEE WHERE DEPARTMENT 642 ORDER BY LOCATION ASC 20  Practice Questions – Table name: Product 1. Write a simple query that will retrieve the product description and product price for all the rows in the table. 2. Write a simple query that will retrieve the product description, product on hand for all the rows with vendor code 232. 3. Write a simple query that will retrieve all the records in the table whose vendor code is not 231. Sort the results by product price in descending order. 21