Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
Physical Entities
Design Physical Entities as it pertains to MS 70-441 Certification
| Question | Answer |
|---|---|
| Describe the process of designing Physical Entities | 1. Determine the data types to be sued by the attributes 2. Design physical table to represent the logical entities 3. Design the entity intregity mechanisms 4. Design the relational integrity mechanisms |
| What are the three categories of data types? | - Structured - Semistructured - Unstructured |
| Describe Structured Data | -Can be organized in groups or entities -Tends to have a well-defined well-known and rigid schema -can b normalized into tables to reduce redundancy -efficient storage & optimized access -easily to define data integrity and business rules against it |
| Semi-Structured Data | - schema not well defined - organized in semantic entities where similar entities are grouped together but may not have the same attributes - xml is an example of this (emails, b2b) - searching is more complex (not as efficient as structured data) |
| Describe Unstructured data | - no schema whatsoever - freeform text, office documents, images are an example of this difficult to search using 'traditional' techniques (+) you can change the format of the data at any time without any major db redesign |
| What are the basic recommendations for data types? | 1. chose the smallest data type possible 2. avoid float & real 3. be careful useing datetime, smalldatetime, due to time component accuracy issues 4. Remember that varchar(max), nvarchar(max), varbinary(max) may slow down performance |
| Describe a few data type sizes... | - bit fields still stores a byte (ie: use char(1) "char one" is more reccomended for fields like sex M, F) - nchar, nvarchar & nText all use 2 bytes to store data - use uniqueidentifier to store GUIDs. |
| What data type should be used to store BLOBS? | varbinary |
| What data type should be used to store free-form text? | VARCAHR & NVARCHAR (pronounced "var char") |
| XML Data Types | exchane as a transport mechanism: -data transport for exchange is excellent (b2b, b2c, A2A) - Document mgmt (office xml and xhtml) - messaging (SOAP, RSS) - middle tier collaboration (+) supports unicode (multi-language),non-proprietary (no one owns |
| What are the three methods of storing XML? | Native XML BLOB Shread (store as in a table) |
| What are the questions to ask "how should I store my XML Data? | - preserve document order and structure? - do I want to query the XML at a finer grain? how often? - do i need to modify? How often? - do I want to index fields? - do I have a schema? - does the solution require system catalog view to admin your sche |
| When should I use the Unyped XML? | - I have no schema for the XML data - I have a schema, but don't want SQL Server to validate the XML |
| When should I use Typed XML? | - i want to have a schema and want SQL Server to validate the XML data - When you want to take advantage of typed storage & querying compilation |
| When I should use User Defined Data Types | - porting database solution that another database system does not support. (ie: boolean access = bit SQL Server) - standardizing data types for commonly used attributes to ensure compatibility |
| when designing entity relationships, what should take most precidence? | performance is important, but referential integrity should take precidence |
| describe Referential Integrity | Ensures the relationships (pk, fk) are always maintained.- are orphaned records are acceptable?- Should the db cascade/update & delete operations?Are constraints or programmatic objects better for enforcing referential integrity in your database? |
| where is the worst eample for implementing referential integrity? (aka: one way to 'lose integrity')... | the application level |
| Pro's of Foreign Key Constraints | - Ease of implentation- performance |
| Cons of foreign Key Constraints | - Constraints only apply within a database- No support for fk with multiple cascade paths and cycles (then you'd have to use a DML trigger to reinforce integrity of data) |
| At what version were cascading update/deletes were implemented in SQL Server | SQL Server 2000 |
| what is the syntax for adding a fk constraint | ALTER TABLE... myTable ADD CONSTRAINT myConstraintFOREIGN KEY (myFKcolumn)REFERENCES referencedTableName(referencedColumn)[ON DELETE {NO ACTION | CASCADE| SET NULL| SET DEFAULT}][ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}] |
| when should you use triggers? | when you will not be able to implement referential integrity through a foreign key constraint. (programmable objects) |
| What should you consider when writing triggers? | you will be firing triggers for all update, and delete transactions. (aka: use a 'DML Trigger') |
| synta for creating a trigger | CREATE TRIGGER myTriggerON {table | view}[WITH <dml trigger option(s)>]{FOR | AFTER| INSTEAD OF}{INSERT, UPDATE ,DELETE}[WITH APPEND][NOT FOR REPLICATION]ASEXTERNAL NAME..<dml trigger option>:: =[ENCRYPTION][EXECUTE AS Clause] |
Created by:
dianagele
Popular Computers sets