BCIS 4660 Exam 1
When a 1:M relationship is mapped, the foreign key must appear in the relation mapped from the M side
True
The ________ slowly changing dimension approach involves creating a previous and current column in the dimension table for each column where changes are anticipated
Type 3
CREATE TABLE Student
DDL
In normalization, there are four main normal forms
False
One of the following is NOT a type of index.
Foreign Index
Which of the following can be used to maintain referential entity integrity?
Foreign Key
Which of the following data warehouse components contains descriptions of the business that the subject (e.g., sales, inventory) belongs to.
Dimensional tables
In which type of Cardinality relationship does one require a junction table?
M:N
To create a simple Database in SQL Server 2019 involves the following steps.
Open SSMS, Right-click on Database, Click New Database, Enter Database Name, OK
You are designing a database for a healthcare software in Canada. The healthcare software has a field [AdmitDate] that will be used to store the date and time that a patient is admitted to a hospital. What data types should you use for this field? Choose all that apply. Hint: there are 2 correct answers
SMALLDATE DATETIME
A relational database is a database structure that allows objects to have relationships with other objects in the database.
True
You are designing a {Student} table for a large university in Texas that will contain more than 60,000 student IDs and EUIDs. This table should keep track of the student ID, EUID, name and other related fields. From your knowledge of the [Student] table at this university, you recognize that the EUID column must be non-numeric, while StudentID column must be numeric. Which T-SQL command should you run? Hint: use your current knowledge of EUID and StudentID requirements
CREATE TABLE Student ( StudentID INT NOT NULL , EUID CHAR (6) NOT NULL, FirstName VARCHAR (50) NOT NULL, LastName VARCHAR (50) NOT NULL, ..... )
ALTER TABLE
DDL
A database table can have a maximum of 1026 columns
False
You work as a data analyst at an IT company with several divisions ( e.g, Servers, Desktops and laptops, Accessories). Each division has its own operational system comprising of marketing, sales, and revenue. The CEO and top leaders of the organization want to see last year's monthly sales of all the organization's divisions. Which of the following solutions is best suited to allow this type of report?
Implementing a data warehouse system that combines the data sources from all the divisions
You are teaching a junior developer how to create a database. Which of the following is not the correct way to create a database in SQL Server.
Script from a SQL Agent
You are designing a database that requires users to write their own SELECT queries against the database from different applications. However, management has determined that users must not directly access the database. As a database engineer, you are tasked to create database objects that are based on tables that will allow the users to write their queries. What database objects should you use?
Views
Match each of the following SQL languages with the correct description. -DDL
statements used for creating and altering
If an entity has three unique attributes, how many primary keys will its mapped relation have?
1
A SQL statement that alters a table is generally called _____.
DDL
One of the following is NOT an objective of business intelligence solution.
Easy access to data that is current, detailed and always modifiable
Anna was just hired as a consultant to design and build a data warehouse system. She makes an assumption that the organization has an operational database system. Is Anna wrong in making this assumption?
No, she is not wrong. You need an operational database system to build a data warehouse system
Which of the following is the correct processing order of the DW Architecture?
Sources, ETL, and DW
You have designed a [Student] table for the Human Resources department. The table has 4 columns (StudentID, LastName, FirstName, EmailAddress). The StudentID column will be used a lot (90% of time) to retrieve student information, followed by the EmailAddress column (50% of time), and then, LastName column ( 20% of time). Which of the columns is the most appropriate to add an index?
StudentID
Microsoft Access is a multi-user RDBMS
False
Which of the following best describes an orphaned record.
A record whose foreign key points to a non-existing primary key value
Which of the following best describes an operational database?
An operational database is a system that captures all elements of the day-to-day activities of the business
A function that returns the number of items in a group, usually in integer is known as _______.
Count
Operational data is known for being_______; while Data warehouse data is known for being ________.
Current; granular
SELECT
DML
You are designing a [Grades] table in a school database. A few of the attributes (fields) in this table include: GradeID, CourseID, SemesterID, StudentID, GradeNumber.... Which of the following data types is better to use for the attribute, GradeNumber?
Decimal
Data warehousing can be implemented and used in these market areas .... Choose all that apply.
Logistics Financial services Retail Manufacturing
Which of the following is NOT a possible cardinality constraint?
Optional Zero
Which of the following column names would not appear in a fact table?
ProductName
Which of the following is not a database object?
Transaction
A data warehouse implementation is a recognition that there is potential business value in organizational data.
True
You are designing a database that requires users to write their own SELECT queries against the database from different applications. However, management has determined that users must not directly access the database. As a database engineer, you are tasked to create database objects that are based on tables that will allow the users to write their queries. What database objects should you use?
Views
Atomicity
all tasks are performed or none
Consistency
must keep database stable
Data warehouses do not reveal past trends that can help organization predict and identify new business areas.
False
The dimensional schema is often referred to as the:
Star schema
A SQL statement that deletes a row in a table is generally referred to a a ________.
DML
UPDATE Student
DML
You are a database developer for Pharagon Inc. You have been asked to discard and drop a table[StudioRate] that is no longer of any use. Write the SQL Syntax that completes this task.
DROP TABLE StudioRate;
Match the following terms with the correct concept. -Subject Oriented
DW data is specific to subject areas
Match the following terms with the correct concept. -Time Variant
DW identifies data by using time periods
Which of the following correctly describe the relationship between Data warehouse and Business intelligence
DW is a subset of BI
Match the following terms with the correct concept. -Integrated
DW is created by combining data from multiple sources
The term "non-volatile" describes which aspect of a data warehouse?
Data cannot be deleted
Match the following terms with the correct concept. -Non-volatile
Data is not deleted or erased
Which of the following can be used to maintain entity [i.e., table] integrity?
Primary Key Constraint
You are designing a table to store demographic information for a genealogy company in the US. The table must store information for the past 400 years. What data types should you use for the [BirthDate] field?
Use the DATETIME
Three main types of SQL statements used for database operations are ____, ____ and ____?
data definition, data manipulation, and data control
Isolation
results only visible after transaction is completed
Match each of the following SQL languages with the correct description. -DCL
statements used for granting and revoking
Match each of the following SQL languages with the correct description. -DML
statements used for manipulating data
Durability
the change is permanent
