K201 Exam 1

Chapter 3: Information Systems 

  • Information Technology (IT) 

    • IT includes the hardware, software, and networks. 

      • Computers, web cameras, video conferencing software. 

    • Supports communication, collaboration, and efficient operations. 

  • Information Systems (IS) 

    • IS includes not only technology but also procedures and people. 

    • IS serves a strategic purpose and supports one or more business processes. 

    • Collects data and delivers valuable information to decision-makers. 

    • They focus on managing information within an entire system, considering the technology and the people. 

  • IS consists of five components: 

  1. Hardware - physical devices. 

    1. Servers 

    2. Computers and Mobile Devices 

    3. Networking Equipment 

  2. Software - programs and applications that process and manage data. 

    1. E-commerce Platform 

    2. Payment Processing Software 

    3. Customer Relationship Management (CRM) Software 

  3. Data raw facts and figures, the input that drives the system. 

    1. Product Information 

    2. Customer Data 

    3. Analystics: 

  4. Procedures - workflows and methods that dictate how tasks are performed efficiently. 

    1. Order Fulfillment Process 

    2. Return and Refund Process 

    3. Security Protocols 

  5. People - users and IT professionals who design, implement and interact with the syste,m. 

    1. Website Managers 

    2. Marketing Team 

    3. Customer Service Representatives 

  • All of these components work together to create a robust and effective system that supports their e-commerce initiative. 

Chapter 4: 

  • Database - an organized collection of data that is stored and accessed electronically. 

    • Allows for efficient retrieval, management, and updating of data, typically through a database management system (DBMS). 

    • Apps that rely on databases are: 

      • Music Streaming Services: Managing user playlists, song libraries, streaming data, and system preferences. 

        • Apple Music 

      • Video Streaming Services - Managing user accounts, viewing history, and recommendations. 

        • Netflix 

      • Social Media Platforms - storing user profiles, photos, likes, comments, and followers. 

        • Tiktok

      • E-commerce Platforms - managing user accounts, product listings, prices, and order history. 

        • Amazon

What is a Relational Database? 

  • Relational Database - a type of database that stores and organizes data in tables. Each table consists of rows and columns. 

    • Tables: These are collections of related data. Each table focuses on one subject like Student or Course.

    • Rows: Each row in a table represents a single record, such as a student's information. 

    • Columns: Each column in a table represents a specific attribute of the data like Name or ID Number 

  • In a relational database, tables can be related to each other using key fields: 

    • Primary Key: A unique identifier for each record in a table 

    • Foreign Key: A field in one table that links to a primary key in another table, establishing a relationship between the two tables.

  • Relational databases use a language called SQL (Structured Query Langugae) to manage and manipulate the data, allowing users to retrieve, update, and organize the information stored in the tables. 

    • All relational databases are databases, but not all databases are relational databases. 

Why are Data Stored in Separate Tables in Relational Databases 

  • Normalization 

    • Avoid Data Redundancy: Data redundancy is minimized by splitting data into separate tables based on related entities. 

      • Ex: Storing customer details in one table and their orders in another prevents repeated customer information for each order. 

    • Maintain Data Consistency: Changes to data only need to be made in one place. 

      • Ex: Updating a customer’s address is done in the customer table, ensuring all related records reflet this change without inconsistency. 

What Benefits Are Associated with Relational Databases? 

  • Improve Data Integrity and Accuracy 

    • Enforce Data Integrity: Relational databases use constraints like primary keys, foreign keys, data types, and data validation to enforce data integrity, which ensures that relationships between tables are valid and that data entries are accurate. 

    • Reduce Anomalies: By organizing data into related tables, update, inset, and delete anomalies are minimized, ensuring that data remains consistent and accurate. 

Efficient Data Retrieval through Queries 

  • Query- request for information from a database, which is crucial for answering important questions such as determining the highest revenue-generating products, evaluating employee performance. 

    • Optimized Queries: SQL allows complex queries to be performed across multiple tables using joins, providing flexible and efficient data retrieval, which enables users to retrieve specific data sets without unnecessary duplication or processing. 

    • Faster Acces: Indexing on separate tables can improve query performance, making data retrieval faster and more efficient. 

Scalability and Flexibility

  • Modular Design 

  • Reusability 

Simplified Maintenance 

  • Easier Updates and Management 

  • Better Organization 

What is a Relational Database Management System (RDBMS)

  • RDBMS is needed to develop a relational database. 

  • It is a software system designed to create, manage, store, and retrieve data organized in a relational data model. 

    • RDBMS programs: 

      • MySQL

        • An open source relational database management system widely used in web applications, including many high profile websites and platforms. 

        • Known for its reliability, ease of use, and broad support in the development community. 

      • Oracle 

        • A powerful, enterprise-grade RDBMS known fr its robustness, scalability, adn extensive feature set. 

        • Commonly used in large enterprises for mission-critical applications, financial systems, and large-scale data processing. 

      • Microsoft SQL Server 

        • A comprehensive RDMS developed by Microsoft, known for its integration with other Microsoft products and services. 

      • IMB DB2

        • An enterprise grade RDBMS developed by IBM offering high performance, reliability, and advanced data management capabilities. 

        • Often usedin large-scale enterprises, financial institutions, and industries requiring robust data processing capabilities. 

What is Microsoft Access? 

  • Microsoft Access is a desktop relational database management system (RDBMS) developed by microsoft. 

  • Benefits of using Access: 

    • Ease of Use 

      • User friendly. 

    • Cost effective 

      • Lower cost. Typically cheaper than SQL server. 

    • Simple Setup and Maintenance 

      • Quick Installation: It can be installed and set up quickly. 

      • Minimal Maintenance: Routine maintenance tasks are simplified, requiring less technical expertise and fewer resources. 

    • Integrated with Microsoft Office

      • Seamless Integration: Access integrates well with other Microsoft Office applications 

    • Rapid Development 

      • Prototyping: Acces is ideal for rapid development and prototyping of database applications, allowing small busineses to quickly create and adapt solutions to their needs. 

      • Forms and Reports: Easy creation of data entry forms. 

    • No Server Requirement

      • Standalone Application: Access does not require a dedicated server or complex infrastructure. Making it easier on laptops etc. 

    • Adequate for Small Data Volumes 

      • Performace: For small data volumes and limited concurrent users, Acces provides adequate performance without the overhead of a full-scale RDBMS like SQL Server. 

Precautions when using Microsfot Access 

  • You need to save your work often when editing hte structure of a database file or creating new objects like a table or query.

  • When an Access file is opened, an additional Access icon with a padloc and a .laccdb extension appears on your computer. This is known as a Microsoft ACcess Lock File and is essential to preventing unwanted changes or deletions by another person. 

  • Once you finish working on the database file be sure to close the file. You must close the file for the new changes to sync back to the cloud. 

  • Creating Relationships 

  • Importing Data 

  • External Data > New Data Source 

Chapter 5 

Data Types

  • Data types determine the kind of values that can be held in a column (field)  and govern the way data is stored, validated, and handled in a relational database. Propert selection of data types can provide the following benefits: 

    • Data Consistency and Validation: Data types ensure that the values stored in a column adhere to specific rules. This consistency helps maintain data integrity and prevents incorrect or incompatible data from being inserted. 

    • Storage Optimization: Different data types have varying storage requirements. Choosing the right data type can minimuze storage overhead. 

    • Query Performance: Data types impact query execution time. Efficient data types lead to faster database operations. 

    • Space Efficieny: Storing data in the most compact format saves space. 

    • Compatibility and Interoperability: Data types should align with application requirements. 

    • Data Integrity Constraints: Data types allowing defining constraints. Constraints maintain data quality. 

  • Types of data found in the Premier Relational Database: 

    • Short text; up to 255 characters Alphanumeric data (names, titles, etc.) 

    • Long text: Up to about 1 GB, large amounts of alphanumeric data: sentences and paragraphs. 

    • Number, 1,2,4,8, or 16 bytes. Numeric data. 

    • Date/Time, 8 bytes, dates and times. 

    • Currency, 8 bytes, monetary data, stored with 4 decimal places of precision. 

    • Autonumber, 4 bytes, unique value generated by acces for each new record. 

    • Yes/No, 1 byte, boolean (true/false) data: Access stores the numeric value zero (0) for false, and -1 for true. 

Metadata- data about data. 

  • Data providing information about one or more aspects of the data. 

Table Relationships

  • Establishing connections between separate tables enables you to manage complex data structures efficiently. 

  • To form a relationship between two tables, they must have one field in common. 

  • The common field will be the primary key of one of the tables and it will be considered a foreign key in the related table. 

    • Ex: In the premiere database certain employees process transactions. A relationship exists between the tblEmployee and tblTransaction tables where the value of EmployeeID (primary key of the tblEmployee) is entered in the ProcessedBy field of the tblTransaction table. 

  • This relationship establishes that an employee (one) can process any number of transactions (many), but transitions can only be processed by one employee. Therefore it is a one to many relationship. 

Referential integrity 

  • Ensures that data references in one table (like the PRocessedBy code in tblTransaction) always correspond to valid entries in another table (an existing EmployeeID in tblEmployee). This prevents the creation of orphan records that point to non-existent data. 

Many to Many Relationships and Junction Tables

  • We use the database to track the classes that employees have enrolled in. Employees can enroll for multiple classes over time and classes can have multiple employees enrolled. This is a many to many relationship. 

  • The most common way to establishing relationships is a junction table. 

  • Junction tables provide a clean and efficient way to manage complex relationships where amny records in one table connect to many records in another. 

Overview of Ch. 5 

Text: letters, symbols, numbers ~

Short Text: field size max = 255 characters

Long Text: up to 65,000 characters / 1 GB

Y/N checkbox ~ 0 No -1 Yes


Number, only numerical data – all numerical data types can be used in calculations (Currency numbers up to 4 decimal places.) (Date/Time numbers displayed as date or time.) (AutoNumber

are auto generated and unique. Often used as a Primary Key field data type.)

If you want to calculate with a field - use number 

  • Quantity, discount percentage, 

If you do not want to calculate and just have the number - use short text 

  • Zip code , phone number, social security , order number 

Notes and comments = long text 


Types of Relationships 

  • One to Many - an employee belongs to one department; one department has many employees. 

  • One to One - A company car is assigned to one employee; an employee is assigned one company car. 

  • Many to many- A class has many students, a student has many classes. 

Table Relationships 

  • Enforce 

    • Enforce Referential Integrity 

      • A product cannot be rated if they ProductID does not exist in tblProduct 

      • A product rating cannot be deleted if that product has ratings. 

  • Cascade 

    • Cascade Update Related Fields 

      • ProductID can be changed in the primary key table and will automatically update for all ratings. 

    • Cascade Delete Related Records 

      • Will allow the deletion of a Product and all its ratings (NEVER set by default) 

Composite Key

  • Two or more fields combined to create a primary key. 

  • Can serve as a constraint to present duplicate records. 

Creating a Lookup using another table’s primary key: 

  • Delete the relationship between the two tables, close relationship table. 

  • Create the Lookup (table design view > Data Types > Lookup Wizard) 

  • Close the table 

  • Edit the relationship 

  • Edit the Lookup in the table design. 

Salary- currency 

Quantity- number 

Phonenumber - short text 

invoiceID - autonumber 

IsPremiumMember - yes/no

TransactionTime - Date/Time 

Location- Short Text 

Memo – Long Text 

Website- hyperlink. 

Chapter 6

  • A field in a database table is used to store values representing a certain attribute of an entity. 

    • Therefore, a database field is like a container that will accept only a specific type of data, like a number, a text string, a data, or a Boolean value such as Yes/No, 1/0, True/False. 

    • Specifying the appropriate data type for a field is the first step to ensuring the accuracy, consistency, and integrity of the data stored in a database. 

  • Field properties are used to specify parameters like the maximum number of characters allowed, the format to be used for displaying values, the range of values that will be accepted, and whether data entry is requred for a given field. 

  • Field size - Since the AutoNumber data type is in fact a Number, the field size is automatically set to be a Long Integer. 

  • New Values- Normally set to Increment, but they can aslo be randomly generated. 

  • Format- Determines how the values are displayed. 

  • Caption- Used to create an alternative label to be displayed instead of the field name. 

  • Indexed- When a field is indexed, sorting and searching is more efficient. Primary keys are always set to Indexed. 

  • Text Align- In MS Access, the contents of the field can be displayed on the left, right, or center of the table cell. 

EmpLastL The Format field property is set to >. Make the values displayed in uppercase. 

EmplBirthDate: The Format property is set to Medium Date. 

EmpState: The Default value, there is an “IN” This will be automatically entered for this field when a new record is created. 

EmpStartDate: In the Validation Rule field property, you see >=#1/1/1995#. (the first hire was not until 1995). THis is a message to be displayed when values do not match the specific requirement. Also states that the StartDate must be on or after Jan 1 1995. 

EmpBonusRate: Double is used to allow numbers with decimal places. 

EmpBonusRate: In the ValidationRule field property you see, “Between 0-0.09”. This is an expression that evaluates to true or false. Values are accepted to be stored in this field only if they are between 0 and .09. 

EmpBonusRate: In the Vald. Text. You see the valid bonus rate..” Result: If a user enters an invalid value, they will see that message. 

EmpFirst and EmpLAst are set as short text data. 

  • Field Size: Short Text data type fields default to the maximum number of characters allowed, which is 255. 

  • Format- The EmpLAst field shows a > character. This forces the values entered in that field to be displayed in upper case. 

  • Required: As the name implies, this propert establishes whether values must be entered when a new record is created. 

Dates are numbers

  • EmpBirthDate, EmpStartDate, and EmpEndDate, fields have the Date/Time Data type. 

    • This data type allows the database to handle certain numbers correctly. Dates have numerica serial value, earlier dates correspond to smaller numbers, while later dates correspond to larger numbers.