Database Flashcards
In the University of Portsmouth, each course contains many students, but each student can register in one course only. Which of the following represent this relationship. Assume that the primary key of for the table COURSE is (C_CODE) and the primary key for the table STUDENT (S_ID). How this relation could be implemented.
Select one:
Put the C_CODE as foreign key in the STUDENT table.
Put the C_CODE as foreign key in the COURSE table.
Create a new entity with the C_CODE and S_ID as composite primary key.
Put the S_ID as foreign key in the COURSE table.
Put the S_ID as foreign key in the STUDENT table.
Put the C_CODE as foreign key in the STUDENT table.
There is an error in the PostgreSQL script below. On what line number is it?
- SELECT *
- IN Client
- WHERE maxRent = 450
- ORDER BY lName;
- IN Client
Fill in the missing word below:
A ________ key is one that is composed of more than one attribute.
Select one:
domain primary foreign composite complex
composite
Which of the following is NOT a standard SQL data type?
Select one:
decimal int text string double
string
A foreign key is an attribute, or set of attributes, within one table that matches the primary key of another table
Select one:
True
False
True
What SQL commands would you use to create a table called Viewing?
Select one:
CREATE TABLE Viewing(clientNo varchar(12) propertyNo varchar(12) viewDate date comment varchar(250));
CREATE TABLE Viewing( varchar(12) clientNo not null, varchar(12) propertyNo not null, date viewDate not null, varchar(250) comment );
CREATE TABLE Viewing( clientNo varchar(12) not null, propertyNo varchar(12) not null, viewDate date not null, comment varchar(250) );
CREATE TABLE Viewing( clientNo: varchar(12) not null, propertyNo: varchar(12) not null, viewDate: date not null, comment: varchar(250) );
CREATE Viewing TABLE( clientNo varchar(12) not null, propertyNo varchar(12) not null, viewDate date not null, comment varchar(250) )
CREATE TABLE Viewing( clientNo varchar(12) not null, propertyNo varchar(12) not null, viewDate date not null, comment varchar(250) );
A composite key can only consist of 2 attributes
Select one:
True
False
False
What PostgreSQL commands would you use to change the branchNo in table Branch to the value B010 for all assistants where their branchNo currently equals the value B005?
Select one:
UPDATE Branch
CHANGE branchNo = ‘B005’
TO branchNo = ‘B010’
UPDATE Branch
SET branchNo = ‘B010’
WHERE branchNo = ‘B005’;
UPDATE Branch
SET branchNo = B010
WHERE branchNo = B005;
SET branchNo = ‘B010’
WHERE branchNo = ‘B005’
IN TABLE Branch;
UPDATE branchNo
SET branchNo = ‘B010’
WHERE branchNo = ‘B005’;
UPDATE Branch
SET branchNo = ‘B010’
WHERE branchNo = ‘B005’;
Assume that you have two entities CAR and OWNER. Each owner can have 1 or many cars but each car is owned by one owner. Which of the following represents this relationship:
Select one:
CAR * ———- 1 OWNER
CAR 1 ———- m OWNER
CAR 1 ———- 1 OWNER
CAR * ———- * OWNER
CAR 1 ———- * OWNER
CAR * ———- 1 OWNER
What does DML stand for?
Select one:
digital management list data manipulation language data management lifecycle digital maximisation language data migration list
data manipulation language
There is an error in the SQL script below. On what line number is it?
- SELECT fName, lName
- FROM Staff
- WHERE gender==’f’
- ORDER BY lName;
- WHERE gender==’f’
Within a table, each primary key value ________. (select all possible answers)
Select one or more:
must be unique is always the first field in each table must be NULL must be numeric must be not NULL all the above
must be unique
must be not NULL
What does DDL stand for?
Select one:
data dictionary list dictionary definition language dictionary data list digital data language data definition language
data definition language
The symbol used to represent an entity in a Crow’s Foot ERD is:
Select one:
a circular shape an oval shape a rectangular box a triangle shape a diamond shape
a rectangular box
Fill in the missing word in the sentence below:
The “ “ clause filters rows to be included in the result table by applying a search condition to the rows of the named table
where
What does DBMS stand for?
Select one:
database management system digital byte management schema database model schema directory block manipulation system database management structure
database management system
NOT NULL means that an attribute is allowed to be empty and does not need to have a value assigned
Select one:
True
False
False
Fill in the missing word in the sentence below:
A data “ “ is a collection of concepts that can be used to describe a set of data, the operations to manipulate the data, and a set of integrity constraints for the data.
model
In the database schema below, the attribute propertyNo in table PropertyForRent is what?
PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
Select one: a composite key a foreign key a partial key a superkey a primary key
a primary key
In the term ERD, the E stands for what?
Select one or more:
a.
End
b.
Entity
c.
Element
d.
Example
Entity
A one to one relationship should be “ “ in any relational database
rare
Each table must have a “ “ key.
A “ “ key value must exist in both tables that have a relationship.
composite logical set secondary primary foreign
- primary
2. foreign
A database would normally be in which normalisation form?
Select one or more:
3rd 2nd 4th 0 1st
3rd
A many to many relationship cannot be implemented as such in a relational database
Select one:
True
False
True
A ________ identifier is a key that is composed of more than one attribute.
Select one:
foreign referential domain composite primary
composite
A foreign key value must exist in both tables that have a relationship.
Select one:
True
False
True
The * symbol denotes a wildcard in SQL
Select one:
True
False
False
What PostgreSQL commands would you use to insert a new record into table Viewing?
Select one:
INSERT INTO Viewing VALUES(‘CR56’, ‘PA14’, ‘01/03/14’, ‘too small’);
INSERT INTO Viewing (‘CR56’, ‘PA14’, ‘01/03/14’, ‘too small’);
PUT INTO Viewing VALUES(‘CR56’, ‘PA14’, ‘01/03/14’, ‘too small’);
INSERT Viewing (‘CR56’, ‘PA14’, ‘01/03/14’, ‘too small’):
SET Viewing VALUES(‘CR56’, ‘PA14’, ‘01/03/14’, ‘too small’);
INSERT INTO Viewing VALUES(‘CR56’, ‘PA14’, ‘01/03/14’, ‘too small’);
What is the correct description for the following business rule?
A car has a single owner. A person can own many cars.
This denotes a “ “ relationship
one to many
Which of the following best describes a database?
Select one:
a collection of data designed to store information
a group of related files sorted alphabetically for easy information retrieval
a shared collection of logically organised data designed to flexibly meet the information needs of an organisation
a unique set of information for management use
None of the above
a collection of data designed to store information
Which of the following is NOT a standard SQL data type?
Select one:
text decimal string double int
string
Which of the following are DDL commands? (PostgreSQL)
Choose 1 or more.
REPLACE SELECT CREATE DROP UPDATE DELETE ALTER
DROP
ALTER
CREATE
Fill in the missing word below:
A ________ key is one that is composed of more than one attribute.
Select one:
complex
domain
foreign
composite
primary
composite
Each database table must have a ________ key.
Select one:
primary
foreign
secondary
logical
primary
What PostgreSQL commands would you use to delete all records from table Registration where the value of staffNo is SL35?
Select one:
DELETE FROM Registration
WHERE staffNo = ‘SL35’;
DELETE IN Registration
WHERE staffNo = ‘SL35’;
DELETE In Registration
WHERE staffNo == ‘SL35’;
DELETE FROM Registration
WHERE staffNo is ‘SL35’
DELETE FROM Registration
WHERE staffNo == ‘SL35’;
DELETE FROM Registration
WHERE staffNo = ‘SL35’;
Which of the following code snippets will successfully create a table called country?
CREATE TABLE NAME COUNTRY (
COUNTRY_ID SERIAL PRIMARY KEY,
COUNTRY_CODE CHAR(2) NOT NULL,
COUNTRY_NAME VARCHAR(15) NOT NULL);
CREATE TABLE COUNTRY (
COUNTRY_ID PRIMARY KEY,
COUNTRY_CODE CHAR(2) NOT NULL,
COUNTRY_NAME VARCHAR(15) NOT NULL);
CREATE TABLE COUNTRY
COUNTRY_ID SERIAL PRIMARY KEY,
COUNTRY_CODE CHAR(2) NOT NULL,
COUNTRY_NAME VARCHAR(15) NOT NULL;
CREATE TABLE COUNTRY (
COUNTRY_ID SERIAL PRIMARY KEY,
COUNTRY_CODE CHAR(2) NOT NULL,
COUNTRY_NAME VARCHAR(15) NOT NULL);
CREATE TABLE COUNTRY (
COUNTRY_ID SERIAL PRIMARY KEY
COUNTRY_CODE CHAR(2) NOT NULL
COUNTRY_NAME VARCHAR(15) NOT NULL);
3.
CREATE TABLE COUNTRY (
COUNTRY_ID SERIAL PRIMARY KEY,
COUNTRY_CODE CHAR(2) NOT NULL,
COUNTRY_NAME VARCHAR(15) NOT NULL);
The primary key in a database table does not have to be unique
Select one:
True
False
False
Fill in the missing word in the sentence below:
” “ DML is a language that allow the user to tell the system what data is needed and exactly how to retrieve it
procedural
The keyword “ “ is used to return one version of an attribute value.
SINGLE DISTINCT ONE ORDER SELECT ONLY
DISTINCT
The keyword “ “ is used to return one version of an attribute value.
Which of the descriptions below best describes a database?
Select one:
A collection of data, software and hardware working together to further business goals
A collection of information that is organized so that it can easily be accessed, managed, and updated
A collection of linked data
A complex infrastructure of layered software to control user access to data
A single, possibly large, repository of data that can be used simultaneously by many departments and users
A single, possibly large, repository of data that can be used simultaneously by many departments and users
NOT NULL means that an attribute is allowed to be empty and does not need to have a value assigned
Select one:
True
False
False
Choose the correct word from the dropdown in the sentence below:
A database “ “ is a description of the database structure
schema
