BCIS 4660- Exam 1 Guide
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
DATETIME and SMALLDATE
A SQL statement that rolls back a transaction is generally referred as a
DCL
A SQL statement that alters a table is generally called
DDL
In dimensional modeling, a table that contains descriptions of a Product, Store, or Customer is known as fact table
False
In normalization, there are four main normal forms
False
Microsoft Access is a multi-user RDBMS
False
The Slow Changing Dimension task is part of Control Flow
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
Operational data is known for being ; while Data warehouse data is known for being .
Current; granular
Data warehouses do not reveal past trends that can help organization predict and identify new business areas.
False
Some objective of business intelligence solutions are:
-Easy access to data that provides managers with the ability to conduct analysis and make decisions -A consolidated historical data repository that supports strategic decision making -A consolidated historical data that facilitates decision making
A database table can have a maximum of ____ columns
1024
Which of the following are NOT valid values for the TinyInt data type
265, 458221
How many columns will Student Entity have?
3
How many entities are depicted by the following requirements? School XYZ keeps track of its 100 students, 10 teachers, and 5 classrooms.
3
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
MATCH ACID acronyms
Atomicity: All tasks in a transaction are performed or none is performed Consistency: A transaction must always keep the database in a stable state Isolation: The result of a transition is only visible after it is committed Durability: When a transaction is committed, the change is permanent
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, ..... )
MATCH SQL statements
CREATE TABLE Student: DDL SELECT: DML UPDATE Student: DML ALTER TABLE: DDL
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
The term "non-volatile" describes which aspect of a data warehouse?
Data cannot be deleted
In the DW/BI Architecture, in which of the following stages or components does dimensional modeling occur?
Data warehouse
Operational database design is based on ___, and data warehouse design is based on __.
ER Modeling, Dimensional Modeling
In the DW/BI Architecture, which of the following components represents data cleaning and loading?
ETL
The set of processes that is used to populate the data warehouse tables with the appropriate relevant data retrieved from the operation databases is known as:
ETL
In which type of Cardinality relationship does one require a junction table?
M:N
Data warehousing can be implemented and used in these market areas...
Manufacturing, Retail, Logistics, Financial services
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 default data source connection manager for SQL Server Database?
OLE DB
To create a simple Database in SQL Server 2019 involves the follow steps
Open SSMS, Right click on Database, Click New Database, Enter Database Name, OK
Which of the following is NOT a possible cardinality constraint?
Optional zero
Match
SSDT/SSIS: ETL Development environment SSMS: ETL administration environment
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
Which of the following is the correct processing order of the DW Architecture?
Sources, ETL, and DW
MATCH
Subject Oriented: DW data is specific to specific area Integrated: DW is created by combining data from many sources Time Variant: DW identifies data by using time periods Non-volatile: Data is not deleted or erased
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
A relational database is a database structure that allows objects to have relationships with other objects in the database
True
The Import and Export Wizard allows us to load data between different sources and destinations outside of SSIS
True
The _____ slowly changing dimension approach creates a new additional dimension record using a new value for the surrogate key every time a value in a dimension record changes
Type 2
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
Choose all that apply. The following are examples of attributes of a fact table
Units Sold, Amount Sold, Quantity
You are designing a table to store demographic information for a genealogy company in the US. The table must store information for the past 250 years. What data types should you use for the [BirthDate] field?
Use the DATETIME
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
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 the data modeling technique
You have users who are not interested in writing queries with lots of joins: Dimensional modeling (OLAP) You support customer service representatives who need access to current data: Entity relationship model (OLTP)
Three main types of SQL statements used for database operations are.....
data definition, data manipulation, data control
An SSIS package typically contains ___, ____, and ____.
data flow, control flow, connection manager