Assignment: Module 3 Use It (Practice)

Each table in a relational database must have exactly one primary key. The most appropriate primary key for the Transactions table could be

Activity_ID (The primary key for a Transactions table could be Activity_ID, which would generate a unique number for each transaction. The Customer_ID field could be a primary key for the Customers table where each customer would have a different ID, but each Customer ID could be used repeatedly in the Transactions table for multiple purchases by the same customer. Similarly, a single transaction might include multiple items (Item_ID), and each Transaction_Date could apply to multiple transactions.)

Structured Query Language (SQL) is a standard fourth-generation query language used by many DBMS packages. The basic format of an SQL query consists of several keywords specifying actions to take. You're configuring an SQL query to pull email information about all customers born after 1985. Complete the following statement: SELECT _____________ FROM _____________ WHERE _____________ What information should go in the first blank after the word SELECT?

Customer_Name, Email (The SELECT keyword defines the fields you want to retrieve for each identified record, such as Customer_Name and Email.)

What information should go in the first blank after the word FROM?

Customers (The FROM keyword defines the tables or files from where the data is retrieved, such as the Customers table.)

You're designing a relational database for a customer loyalty app you're developing for a client. The client has been saving information in a series of Excel spreadsheets, but the volume of data is becoming too unwieldy to use spreadsheets effectively. Before you can import the data from the client's existing records, you need to set up some tables with fields and records. Which tables will you need in your database for the purposes of this app?

Customers and Transactions

Your organization, which sells manufacturing parts to companies in 17 different countries, is in the process of migrating your inventory database to the cloud. This database stores raw data on available inventory at all of your company's warehouses globally. The database will be accessible from multiple cloud-based servers that reside on your cloud provider's hardware located in three regions around the world. Each redundant database will contain a fully synchronized copy of all data to accelerate customer access to the information, regardless of the customer's geographical location. What database trend is your company implementing?

Distributed database (A distributed database stores data on multiple servers sometimes placed in distinct geographical locations.)

Client: That sounds like a lot of data. Won't the database get too big to find information quickly? You: Good question. We can build in a(n) ________ or two to help speed up queries. It will take up a bit more space, but will greatly increase efficiency for user interactions.

Index (An index lists an indexed value and a pointer to the disk location of the record matching that value. An index speeds up data access in a database, and can be designed to accelerate the most commonly used database queries.)

You're developing a customer loyalty app for a small chain of retail stores. The app will need a backend database to maintain customer information. While designing the app, you're conducting a planning meeting with your client to determine the scope of the project and resolve questions she has about the project. Client: I'm trying to understand how all this will work—how the app and the database will work together. Where will you get the information for the database? You: This data is specific to your company, so it's called ________ data. Your customers will enter their personal information when they create their accounts. Then sales transactions will continue to add data about each customer over time.

Internal (Internal data is collected from within an organization's own business processes and can include transaction records, sales records, personnel records, and customer-reported information.)

Which of the following fields should be included in the Customers table? Choose two.

Last_Name and Customer_ID (Each field should identify a simple and complete attribute directly relevant to that record. From these examples, Last_Name and Customer_ID meet these requirements. The Address field is not specific enough; it should be broken into separate fields, such as Street_Address, City, State, and Zip_Code. The Transaction_Date field belongs in the Transactions table.)

Client: I suppose "users" include customers and employees. Will they all see the same information about each account? You: No, we'll design separate ______ for each type of user so the user gets exactly the needed information and nothing more. This is especially important when considering customer privacy.

Logical views (A logical view determines how information appears to users and how it can be organized and retrieved.)

The first step in database design is defining a data model, which determines how data is created, represented, organized, and maintained. The data model usually defines the database's data structure, operations, and integrity rules. Identify the data model used in each scenario.

Relational = A database of contestants in a dog show where each dog, owner, and competition is identified by a primary key Hierarchical = A database that stores detailed information on the taxonomy of living things found in a geographical area, including the categories of kingdom, phylum, class, order, family, genus, and species Object oriented = A database used to catalog types of architecture, geographical features, and sociological characteristics in various parts of the world Network = A database used to cross-reference actors who have performed in various movies with directors, writers, and other support staff for those movies (A relational database uses two-dimensional tables of rows (records) and columns (fields), where each record, such as dogs, owners, and competitions in a dog show, is uniquely identified by a primary key. An object-oriented database uses database objects to represent complex information about real-world entities where there might not be much consistency in the data from object to object, such as the variable information that might be collected on architecture, geographical features, and sociological characteristics of different geopolitical areas throughout the world. A hierarchical database shows relationships among records in a treelike structure (hierarchy), such as the taxonomy of living things where a species is part of a genus, which is part of a family, which is part of an order, etc. A network database can show multiple parent and child records for each record, such as when an actor has appeared in multiple movies, and each movie results from the work of multiple actors, writers, and other support staff.)

What DDBMS approach allows the database to be implemented in this way?

Replication (The replication approach to a distributed DBMS has each site store a complete copy of the data in the organization's database.)

What information should go in the first blank after the word WHERE?

Year_of_Birth>1985 (The WHERE keyword lists conditions (criteria) for retrieving data. In this case, you want all customers born after 1985. This means you want all customers whose birth year is greater than 1985. The statement "greater than 1985" is written logically as Year_of_Birth>1985.)