BCIS exam 1
Which of the following can be used to maintain referential entity integrity?
Foreign Key
A relational database is a database structure that allows objects to have relationships with other objects in the database. True or False
True
Match the following SQL Statements to the appropriate type: -CREATE TABLE Student: -SELECT: -UPDATE Student: -ALTER TABLE:
DDL DML DKN
Which of the following can be used to maintain entity [i.e., table] integrity? A)Primary Key Constraint B)Index Constraint C)Alter Table Constraint D)View Constraint
a
Which of the tools in SQL Server Management Studio allows one to visualize an ER Model or relationships between tables? A)Database Diagram B)ER Model Diagram C)Database Tables D)Power BI
a
Which of the following are NOT valid values for the TinyInt data type? Choose all that apply -265 -45821 -30 -250
-265 -45821
Match the following terms with the correct concept. -Subject Oriented: -Integrated: -Time variant: -Non-volatile:
-Subject Oriented :DW data is specific to subject areas -Integrated: DW is created by combining data from many different sources -Time variant: DW identifies data by using time periods -Non-volatile: Data is not deleted or erased
Select all correct answers. The create table syntax should include the following: -The data type of the column name -Only one parenthesis -A comma at the end of the last column name definition -Create Table
-The data type of the column name -A comma at the end of the last column name definition -Create Table
Match the following characteristics and modeling techniques: 1-Used in operational databases 2-Used in data warehousing 3-Normalized tables 4-De-normalized tables
1- ER model 2-Dimensional model 3-ER model 4-Dimensional model
The following are attributes in a fact table. Select all that apply. Unit Price Product Weight Units Sold Product Color
Unit Price, Units sold
To create a simple Database in SQL Server 2019 involves the following steps. A)Open SSMS, Right-click on Database, Click New Database, Enter Database Name, OK B)Right-click on Database, Open SSMS, Click New Database, Enter Database Name, OK C)Open SSMS, Click New Database, Right-click on Database, Enter Database Name, D)OKRight-click on Database, Open SSMS, Click New Database, Enter Database Name, OK
a
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? A)Indexes B)Views C)Check Constraints D)Table Functions
b
One of the following is NOT a type of index. A)Clustered Index B)Unique Index C)Foreign Index D)Nonclustered Index
c
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? A)Use the Varchar (10) data type B)Use the Char (10) data type C)Use the DATETIME D)Use the SMALLDATE
c
A SQL statement that rolls back a transaction is generally referred to as a ________. A)JBL B)DML C)DDL D)DCL
d
Match the data modeling technique 1-You have users who are not interested in writing queries with lots of joins: 2-You support customer service representatives who need access to current data:
1-Dimensional modeling (OLAP) 2-Entity relationship model (OLTP)
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 A)Use the DATETIME B)Use the Char (10) data type C)Use the SMALLDATE D)Use the Varchar (10) data type
a, c
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? A)Check Constraints B)Table Functions C)Indexes D)Views
d
A SQL statement that alters a table is generally called _____. DDL or DML
ddl
Match each of the following languages with the correct description. -DDL: -DML: DCL:
DDL: Statements used for creating and altering database objects DML: Statements used for manipulating data DCL: Statements used for granting or revoking database privileges
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
Which of the following can be used to maintain entity [i.e., table] integrity?
Primary Key Constraint
Choose all that apply. The following are examples of attributes of a fact table. DiscountID StoreID Quantity Amount sold Units Sold
Quantity Amount sold Units Sold
A function that returns the number of items in a group, usually in integer is known as _______. A)Count B)Min C)Sum D)Avg
a
In the DW/BI Architecture, in which of the following stages or components does dimensional modeling occur? A)Data warehouse B)BI Application C)Operational data sources D)ETL
a
The set of processes that is used to populate the data warehouse tables with the appropriate relevant data retrieved from the operational databases is known as: A)ETL B)BI C)OLAP D)OLTP
a
Which of the following best describes an operational database? A)An operational database is a system that captures all elements of the day-to-day activities of the business B)An operational database is a system that captures both the day-to-day and historical business activities C)An operational database is a system that captures and transforms data from different data sources D)An operational database is a system that captures and stores historical activities in the business
a
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? A)Implementing a data warehouse system that combines the data sources from all the divisions B)Running reports from each division's operational system and then using Excel to combine them C)Restoring backups of each of the division's database and then querying and combining the results D)Implementing a statistical analysis of each division's operational data for each month
a
A SQL statement that alters a table is generally called _____. A)DDM B)DDL C)DML D)DCL
b
How many entities are depicted by the following requirements? School XYZ keeps track of its 100 students, 10 teachers, and 5 classrooms. A)115 B)3 C)114 D)4
b
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. A)Type 4 B)Type 2 C)Type 3 D)Type 1
b
The term "non-volatile" describes which aspect of a data warehouse? A)Data from different time-zones B)Data cannot be deleted C)Historical data D)Data from non stable sources
b
Which of the following best describes an orphaned record ? A)A column whose foreign key points to a non-existing primary key value B)A record whose foreign key points to a non-existing primarv key value C) A record whose foreign key points to an existing primary key value D) A column whose foreign key points to an existing primary key value
b
Which of the following correctly describe the relationship between Data warehouse and Business intelligence A)BI and DW are part of Microsoft Excel B)DW is a subset of BI C)BI is a subset of DW D)BI and DW both mean the same thing
b
Which of the following is the correct processing order of the DW Architecture? A)Sources, ETL, and BI Apps B)Sources, ETL, and DW C)DW, BI Apps, and ETL D)ETL, Sources, and DW
b
You are a data warehouse analyst, who is mentoring a junior analyst. In trying to explain the function of surrogate key to the junior analyst, how would you best describe it? A)The surrogate key, a crucial part of the dimension table serves as a unique key B)The surrogate key, a meaningless part of the dimension table serves as a PK C)The surrogate key, a crucial part of the dimension table is used to ensure entity integrity D)The surrogate key, a meaningless part of the fact table serves as a PK
b
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? A)LastName B)StudentID C)FirstName D)EmailAddress
b
One of the following is NOT an objective of business intelligence solution. A)Easy access to data that provides managers with the ability to conduct analysis and make decisions B)A consolidated historical data that facilitates decision making C)Easy access to data that is current detailed and always modifiable D)A consolidated historical data repository that supports strategic decision making
c
Operational data is known for being ________; while Data warehouse data is known for being ________________. A)Historical; modifiable B)Historical; not usually modifiable C)Current; granular D)Current; modifiable
c
The ________ slowly changing dimension approach involves creating a previous and current column in the dimension table for each column where changes are anticipated A)Type 0 B)Type 1 C)Type 3 D)Type 2
c
Three main types of SQL statements used for database operations are ____, ____ and ____? A)data definition, data manipulation, and data committing B)data commands, data manipulation, and data query C)data definition, data manipulation, and data control D)data manipulation, data control, and data commands
c
Which of the following can be used to maintain referential entity integrity? A) Join B)View C)Foreign Key D)Primary Key
c
Which of the following is not a database object? A)Views B)Index C)Transaction D)Table
c
In the DW/BI Architecture, which of the following components represents data cleaning and loading? A)BI Application B)Operational data sources C)Data warehouse D)ETL
d
Which of the following column names would not appear in a fact table? A)SalesTransactionID B)TimeOfDay C)UnitsSold D)ProductName E)Product Key (FK)
d
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? A)Bit B)Money C)Integer D)Decimal
d
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 A)CREATE TABLE Student ( StudentID CHAR (6), EUID INT, FirstName CHAR (50) NOT NULL, LastName CHAR (50) NOT NULL, ..... ) B)CREATE TABLE Student ( StudentID INT (6), EUID INT, FirstName CHAR (50), LastName CHAR (50), ..... ) C)CREATE TABLE Student ( StudentID CHAR (6), EUID INT, FirstName CHAR (50), LastName CHAR (50), ..... ) D)CREATE TABLE Student ( StudentID INT NOT NULL , EUID CHAR (6) NOT NULL, FirstName VARCHAR (50) NOT NULL, LastName VARCHAR (50) NOT NULL, ..... )
d
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. A)Write a Create Database script B)Use a template script C)Script from an existing database D)Script from a SQL Agent
d
A database table can have a maximum of 1026 columns True or False
false
Data warehouses do not reveal past trends that can help organization predict and identify new business areas. True or False
false
In dimensional modeling, a table that contains descriptions of a Product, Store, or Customer is known as fact table. true or false
false
Microsoft access is a multi-user RDBMS true or false
false
A data warehouse implementation is a recognition that there is potential business value in organizational data. True or False
true
The what, who, when, and where are questions that help us determine the dimension tables true or false
true
