Download INMT 342 Exam 2: Database Normalization, SQL, and Data Manipulation and more Exams Nursing in PDF only on Docsity!
INMT 342 Exam 2 Questions and Correct
Solutions
normalization - Answer-the process of grouping attributes together into tables it helps to validate and improve logical database design to satisfy constraints and avoid duplication of data what do well structured tables avoid? - Answer-insertion anomalies update anomalies deletion anomalies redundancy functional dependency - Answer-the value of one attributes determines the value of other attributes a well-structured table has a single functional dependency; an individual's SSN can tell about every attribute in the table normalization: 0 normal form - Answer-data is not in table format
identifier is not defined or bad table has multi-valued attributes normalization: 1 normal form - Answer-good identifier no multi-valued attributes (fill in blanks or separate lists) partial functional dependency - Answer-the value of non-key fields are functionally dependent on only part of the primary key field multiple primary keys that each tell different things about the table; need to be split up normalization: 2 normal form - Answer-already in 1st normal form has no partial functional dependencies go from 1NF to 2NF by breaking the original table into two related tables (functional dependency) every non-key field must be dependent on the entire primary key transitive dependency - Answer-the value of non-key fields are functionally dependent on other non- key fields ex: salary class field determines what goes in salary field normalization: 3 normal form - Answer-already in 2nd normal form no transitive dependencies
associated with the STRUCTURE of the database CREATE, ALTER, DROP data control language (DCL) - Answer-commands used to control a database, including administering privileges and the committing of data associated with ACCESS, CONTROL, AND SECURITY of the database GRANT, REVOKE data manipulation language (DML) - Answer-commands used to maintain and query a database, including updating, inserting, modifying, and querying data associated with managing and viewing the data inside the database INSERT, UPDATE, DELETE, SELECT CREATE - Answer-allows you to create database structures mimics the structure of the logical relation data types (domain integrity): character - Answer-CHAR(n): n specifies number of letters - fixed length string VARCHAR(n): n specifies max number of letters - variable length string NCHAR(n): n specifies number of letters, can handle unicode of letters NVARCHAR(n): n specifies the maximum number of letters, can handle unicode of letters
data types (domain integrity): number - Answer-use for fields that you might do calculations on; sometimes PK/FK need to be INT to allow auto-numbering INT: can handle integer DECIMAL(p,s): p specifies total number of digits, s specifies the number of digits after the decimal MONEY: can handle monetary value data types (domain integrity): date & time - Answer-SQL server accepted date formats: 2021-10-12 ; 2021-OCT-12 ; 10-21- DATE: 'YYYY-MM-DD' TIME: 'hh:mm:ss' DATETIME: 'YYYY-MM-DD hh:mm:ss' DATETIME2: ''YYYY-MM-DD hh:mm:ss' (larger date range and default fractional precision) foreign key constraints - Answer-identify what will happen in the FK table when the PK record is updated or deleted in the PK table (default is NO ACTION) NO ACTION: do not allow update or delete of associated PK field CASCADE: update or delete the PK record and all associated FK records SET NULL: set the field value in the FK null (only when the field not required) SET DEFAULT: update the FK field to the default value (works only when there is a predetermined default value) CHECK constraint - Answer-limits allowable values in a field
ALTER TABLE Customer DROP COLUMN Country; ex: adding a primary key ALTER TABLE Inventory ADD CONSTRAINT InventoryPK PRIMARY KEY(InventoryID); ex: adding a composite primary key ALTER TABLE Inventory ADD CONSTRAINT OrdersPK PRIMARY KEY (InventoryID, CustID); ex: adding a foreign key ALTER TABLE Inventory ADD CONSTRAINT OrdersInventoryFK FOREIGN KEY (InventoryID) REFERENCES Inventory(InventoryID) ON UPDATE CASCADE ON DELETE NO ACTION existing data must work with new definition! DCL functions - Answer-GRANT - allows privileges DENY - disallows privileges REVOKE - removes privileges
DML functions - Answer-INSERT: add rows to a table UPDATE: change rows on which a specific condition is true (can be used with a where clause to indicate which rows to change) DELETE: delete rows from the database SELECT: used to query the database (answer business questions) COUNT() - Answer-count the number of rows in a database table MAX() - Answer-highest value for a certain column MIN() - Answer-lowest value for a certain column AVG() - Answer-average value for a certain column SUM() - Answer-aggregate function totals value in a numeric column GETDATE() - Answer-returns current date and time as a DATETIME no input inside parentheses ex: SELECT GETDATE() FROM INVENTORY MONTH() - Answer-returns an integer that represents the month of the specified date YEAR() - Answer-returns an integer that represents the year of the specified date
HAVING (conditions) - a particular aggregate condition or set of aggregate conditions is satisfied (cannot be used without GROUP BY) (group - level criteria / aggregate comparison) ORDER BY (columns) - attributes used to sort output (ascending = ASC, descending = DESC)
