Choose a business that interests you.?? You will be using this business as a reference point throughout the course.? Follow the instructions on p38, and create a list of business ru
In 500 words.
Choose a business that interests you. You will be using this business as a reference point throughout the course. Follow the instructions on p38, and create a list of business rules. Then, create 5 business rules for your company. Submit a Word document including 5 business rules and their related components to the online class.
Remember the format is [Person, Place, or Thing] performs some action to [one or many] [Person, Place, or Thing]
In the document, explain the relationship between the components and the business rules. Include information about why this step is important. Feel free to include tables and graphics as appropriate.
Source links:
https://www.techtarget.com/searchdatamanagement/definition/database-management-system
https://www.cio.com/article/228901/what-is-predictive-analytics-transforming-data-into-future-insights.html
Note: Strictly No copy paste, Plagiarism results in course termination. Please include references.
1
Database Modeling and Design
3 rd Edition
Toby J. Teorey University of Michigan
Lecture Notes
Contents
I. Database Systems and the Life Cycle (Chapter 1)……………………2 Introductory concepts; objectives of database management 2 Relational database life cycle 3 Characteristics of a good database design process 7
II. Requirements Analysis (Chapter 3)………………………………….8
III. Entity-Relationship (ER) Modeling (Chapters 2-4)……………… 1 1 Basic ER modeling concepts 11 Schema integration methods 22 Entity-relationship 26 Transformations from ER diagrams to SQL Tables 29
IV. Normalization and normal forms (Chapter 5)………………………35 First normal form (1NF) to third normal form (3NF) and BCNF 35 3NF synthesis algorithm (Bernstein) 42 Fourth normal form (4NF) 47
V. Access Methods (Chapter 6)…………………………..………………50 Sequential access methods 50 Random access methods 52 Secondary Indexes 58 Denormalization 62 Join strategies 64
VI. Database Distribution Strategies (Chapter 8)……………………….66 Requirements of a generalized DDBMS: Date’s 12 Rules 68 Distributed database requirements 72 The non-redundant “ best fit” method 74 The redundant “all beneficial sites” method 77
VII. Data Warehousing, OLAP, and Data Mining (Chapter 9)………..79 Data warehousing 79 On-line analytical processing (OLAP) 86 Data mining 93
Revised 11/18/98 – modify Section V Revised 11/21/98 – insertions into Section VII Revised 1/14/99 – modify Section VI Revised 2/11/99 – modify Section IV, 4NF (p.47 FD, MVD mix) Revised 6/13/00 – modify Section V (secondary indexes)
2
I. Database Systems and the Life Cycle
Introductory Concepts
data—a fact, something upon which an inference is based (information or knowledge has value, data has cost)
data item—smallest named unit of data that has meaning in the real world (examples: last name, address, ssn, political party)
data aggregate (or group) — a collection of related data items that form a whole concept; a simple group is a fixed collection, e.g. date (month, day, year); a repeating group is a variable length collection, e.g. a set of aliases.
record—group of related data items treated as a unit by an application program (examples: presidents, elections, congresses)
file—collection of records of a single type (examples: president, election)
database—computerized collection of interrelated stored data that serves the needs of multiple users within one or more organizations, i.e. interrelated collections of records of potentially many types. Motivation for databases over files: integration for easy access and update, non-redundancy, multi-access.
database management system (DBMS) — a generalized software system for manipulating databases. Includes logical view (schema, sub-schema), physical view (access methods, clustering), data manipulation language, data definition language, utilities – security, recovery, integrity, etc.
database administrator (DBA) — person or group responsible for the effective use of database technology in an organization or enterprise. Motivation: control over all phases of the lifecycle.
Objectives of Database Management
1. Data availability—make an integrated collection of data available to a wide variety of users
* at reasonable cost—performance in query update, eliminate or control data redundancy * in meaningful format—data definition language, data dictionary * easy access—query language (4GL, SQL, forms, windows, menus); embedded SQL, etc.; utilities for editing, report generation, sorting
2. Data integrity—insure correctness and validity * checkpoint/restart/recovery * concurrency control and multi-user updates * accounting, audit trail (financial, legal)
3. Privacy (the goal) and security (the means) * schema/sub-schema, passwords
4. Management control—DBA: lifecycle control, training, maintenance
3
5. Data independence (a relative term) — avoids reprogramming of applications, allows easier conversion and reorganization
* physical data independence—program unaffected by changes in the storage structure or access methods
* logical data independence—program unaffected by changes in the schema
* Social Security Administration example (1980ís) – changed benefit checks from $999.99 to $9999.99 format – had to change 600 application programs – 20,000 work hours needed to make the changes (10 work years)
* Student registration system—cannot go to a 4-digit or hexadecimal course numbering system because of difficulty changing programs
*Y2K (year 2000) problem—many systems store 2-digit years (e.g. ‘02-OCT-98’) in their programs and databases, that give incorrect results when used in date arithmetic (especially subtraction), so that ‘00’ is still interpreted as 1900 rather than 2000. Fixing this problem requires many hours of reprogramming and database alterations for many companies and government agencies.
Relational Database Lifecycle
1. Requirements formulation and analysis * natural data relationships (process-independent) * usage requirements (process-dependent) * hardware/software platform (OS, DBMS) * performance and integrity constraints * result: requirements specification document, data dictionary entries
2. Logical database design 2.1 ER modeling (conceptual design) 2.2 View integration of multiple ER models 2.3 Transformation of the ER model to SQL tables 2.4 Normalization of SQL tables (up to 3NF or BCNF) *result: global database schema, transformed to table definitions
3. Physical database design * index selection (access methods) * clustering
4. Database distribution (if needed for data distributed over a network) * data fragmentation, allocation, replication
5. Database implementation, monitoring, and modification
4
5
6
7
Characteristics of a Good Database Design Process
* iterative requirements analysis – interview top-down – use simple models for data flow and data relationships – verify model
* stepwise refinement and iterative re-design
* well-defined design review process to reduce development costs review team -database designers
-DBMS software group -end users in the application areas when to review – after requirements analysis & conceptual design – after physical design – after implementation (tuning) meeting format – short documentation in advance – formal presentation – criticize product, not person – goal is to locate problems, do solutions off line – time limit is 1-2 hours
8
II. Requirements Analysis
Purpose – identify the real-world situation in enough detail to be able to define database components. Collect two types of data: natural data (input to the database) and processing data (output from the database).
Natural data requirements (what goes into the database)
1. Organizational objectives – sell more cars this year – move into to recreational vehicle market
2. Information system objectives – keep track of competitors’ products and prices – improve quality and timing of data to management regarding production schedule delays, etc. – keep track of vital resources needed to produce and market a product
3. Organizational structure/chart
4. Administrative and operational policies – annual review of employees – weekly progress reports – monthly inventory check – trip expense submission
5. Data elements, relationships, constraints, computing environment
Processing requirements (what comes out of the database)
1. Existing applications – manual, computerized
2. Perceived new applications
* quantifies how data is used by applications
* should be a subset of data identified in the natural relationships (but may not be due to unforeseen applications)
* problem – many future applications may be unknown
9
Data and Process Dictionary Entries for Requirements Analysis in the Database Design Lifecycle Entity Description (possibly in a data dictionary) Name customer Reference-no 4201 Cardinality 10,000 Growth rate 100 per month Synonyms user, buyer Role (or description) someone who purchases or rents a
product made by the company. Security level 0 (customer list is public) Subtypes adults, minors Key attribute(s) cust-no Non-key attribute(s) cust-name, addr, phone, payment-status Relationship to other entities salesperson, order, product Used in which applications billing, advertising
Attribute description (data elements in a data dictionary) Name cust-no Reference-no 4202 Range of legal values 1 to 999,999 Synonyms cno, customer-number Data type integer Description customer id number set by the company. Key or nonkey key Source of data table of allowable id numbers Used in applications billing Attribute trigger /*describes actions that occur when a
data element is queried or updated*/
Relationship description Name purchase Reference-no 511037 Degree binary Entities and connectivity customer(0,n), product(1,n) Synonyms buy Attributes (of the relationship) quantity, order-no Assertions a customer must have purchased at
least one product, but some products may not have been purchased as yet by any customers.
Process (application) description Name payroll Reference-no 163 Frequency bi-weekly Priority 10 Deadline noon Fridays Data elements used emp-name, emp-salary Entities used employee Data volume (how many entities) implicit from entity cardinality
10
Interviews at different levels
Top management – business definition, plan/objectives, future plans
Middle management – functions in operational areas, technical areas, job-titles, job functions
Employees – individual tasks, data needed, data out
Specific end-users of a DBMS – applications and data of interest
Basic rules in interviewing
1. Investigate the business first
2. Agree with the interviewee on format for documentation (ERD, DFD, etc.)
3. Define human tasks and known computer applications
4. Develop and verify the flow diagram(s) and ER diagram(s)
5. Relate applications to data (this helps your programmers)
Example: order entry clerk
Function: Take customer orders and either fill them or make adjustments. Frequency: daily
Task Def Volume Data Elements
1. Create order 2000 A, B, E, H 2. Validate order 2000 A, B, G, H, J 3. Fill out error form 25 A, C 4. Reserve item/price 6000 A, D, H 5. Request alternate items 75 A, E, I, K,M 6. Enter unit price 5925 A, F, J, N
11
III. Entity-Relationship (ER) Modeling
Basic ER Modeling Concepts
Entity – a class of real world objects having common characteristics and properties about which we wish to record information.
Relationship – an association among two or more entities
* occurrence – instance of a relationship is the collective instances of the related entities
* degree – number of entities associated in the relationship (binary, ternary, other n-ary)
* connectivity – one-to-one, one-to-many, many-to-many
* existence dependency (constraint) – optional/mandatory
Attribute – a characteristic of an entity or relationship
* Identifier – uniquely determines an instance of an entity
* Identity dependence – when a portion of an identifier is inherited from another entity
* Multi-valued – same attribute having many values for one entity
* Surrogate – system created and controlled unique key (e.g. Oracle’s “create sequence”)
12
13
14
15
Super-class (super-type)/subclass (subtype) relationship
Generalization
* similarities are generalized to a super-class entity, differences are specialized to a subclass entity, called an “ISA” relationship (“specialization” is the inverse relationship)
* disjointness constraint – there is no overlap among subclasses
* completeness constraint – constrains subclasses to be all-inclusive of the super-class or not (i.e. total or partial coverage of the superclass)
* special property: hierarchical in nature
* special property: inheritance – subclass inherits the primary key of the super-class, super-class has common nonkey attributes, each subclass has specialized non-key attributes
Aggregation
* “part-of” relationship among entities to a higher type aggregate entity (“contains” is the inverse relationship)
* attributes within an entity, data aggregate (mo-day-year)
* entity clustering variation: membership or “is-member-of” relationship
16
Constraints
17
Constraints in ER modeling
* role – the function an entity plays in a relationship
* existence constraint (existence dependency) – weak entity
* exclusion constraint – restricts an entity to be related to only of several other
* entities at a given point in time
– mandatory/optional – specifies lower bound of connectivity of entity instances – participating in a relationship as 1 or 0
* uniqueness constraint – one-to-one functional dependency among key attributes in a relationship: binary, ternary, or higher n-ary
18
19
20
21
22
Schema Integration Methods
Goal in schema integration – to create a non-redundant unified (global) conceptual schema
(1) completeness – all components must appear in the global schema (2) minimality – remove redundant concepts in the global schema (3) understandability – does global schema make sense?
1 . Comparing of schemas
* look for correspondence (identity) among entities
* detect possible conflicts
– naming conflicts homonyms – same name for different concepts synonyms – different names for the same concept
– structural conflicts type conflicts – different modeling construct for the same concept (e. g. “order” as an entity, attribute, relationship)
– dependency conflicts – connectivity is different for different views (e.g. job-title vs. job-title-history)
– key conflicts – same concept but different keys are assigned (e.g. ID-no vs. SSN)
– behavioral conflicts – different integrity constraints (e.g. null rules for optional/mandatory: insert/delete rules)
* determine inter-schema properties
– possible new relationships to combine schemas
– possible abstractions on existing entities or create new super-classes (super-types)
2. Conforming of schemas
* resolve conflicts (often user interaction is required)
* conform or align schemas to make compatible for integration
* transform the schema via – renaming (homonyms, synonyms, key conflicts) – type transformations (type or dependency conflicts) – modify assertions (behavioral conflicts)
3. Merging and restructuring
* superimpose entities
* restructure result of superimposition
23
24
25
26
Entity-Relationship Clustering
Motivation
* conceptual (ER) models are difficult to read and understand for large and complex databases, e.g. 10,000 or more data elements
* there is a need for a tool to abstract the conceptual database schema (e. g. clustering of the ER diagram)
* potential applications
– end user communication
– application design team communication
– documentation of the database conceptual schema (in coordination with the data dictionary)
Clustering Methodology
Given an extended ER diagram for a database…..
Step 1. Define points of grouping within functional areas.
Step 2. Form entity clusters * group entities within the same functional area * resolve conflicts by combining at a higher functional grouping
Step 3. Form higher entity clusters.
Step 4. Validate the cluster diagram. * check for consistency of interfaces. * end-users must concur with each level.
27
28
29
Transformations from ER diagrams to SQL Tables
* Entity – directly to a SQL table
* Many-to-many binary relationship – directly to a SQL table, taking the 2 primary keys in the 2 entities associated with this relationship as foreign keys in the new table
* One-to-many binary relationship – primary key on “one” side entity copied as a foreign key in the “many” side entity’s table
* Recursive binary relationship – same rules as other binary relationships
* Ternary relationship – directly to a SQL table, taking the 3 primary keys of the 3 entities associated with this relationship as foreign keys in the new table
* Attribute of an entity – directly to be an attribute of the table transformed from this entity
* Generalization super-class (super-type) entity – directly to a SQL table
* Generalization subclass (subtype) entity – directly to a SQL table, but with the primary key of its super-class (super-type) propagated down as a foreign key into its table
* Mandatory constraint (1 lower bound) on the “one” side of a one-to-many relationship – the foreign key in the “many” side table associated with the primary key in the “one” side table should be set as “not null” (when the lower bound is 0, nulls are allowed as the default in SQL)
30
31
32
33
34
35
IV. Normalization and Normal Forms
First normal form (1NF) to third normal form (3NF) and BCNF
Goals of normalization 1. Integrity 2. Maintainability
Side effects of normalization * Reduced storage space required (usually, but it could increase) * Simpler queries (sometimes, but some could be more complex) * Simpler updates (sometimes, but some could be more complex)
First normal form (1NF) — a table R is in 1NF iff all underlying domains contain only atomic values, i.e. there are no repeating groups in a row.
functional dependency —given a table R, a set of attributes B is functionally dependent on another set of attributes A if at each instant of time each A value is associated with only one B value. This is denoted by A -> B. A trivial FD is of the form XY –> X (subset).
super-key – – a set of one or more attributes, which, when taken collectively, allows us to identify uniquely an entity or table.
candidate key —any subset of the attributes of a super-key that is also a super-key, but not reducible.
primary key – – arbitrarily selected from the set of candidate keys, as needed for indexing.
Third normal form (3NF) A table is in 3NF if, for every nontrivial FD X –> A, either:
(1) attribute X is a super-key, or (2) attribute A is a member of a candidate key (prime attribute)
Boyce-Codd normal form (BCNF) A table is in BCNF if, for every nontrivial FD X –> A,
(1) attribute X is a super-key.
36
Tables, Functional Dependencies, and Normal Forms
First Normal Form TABLE SUPPLIER_PART (100k rows, 73 bytes/row => 7.3 MB) S N U M SNAME STATUS CITY
PNUM PNAME WT QTY S1 SMITH 20 LONDON P1 S1 SMITH 20 LONDON P2 S1 SMITH 20 LONDON P3 S1 SMITH 20 LONDON P4 S1 SMITH 20 LONDON P5 S1 SMITH 20 LONDON P6 S2 JONES 10 PARIS P1 S2 JONES 10 PARIS P2 S3 BLAKE 10 PARIS P3 S3 BLAKE 10 PARIS P5
S4 CLARK 20 LONDON P2 BOLT 22 2 10-31-89 S4 CLARK 20 LONDON P4 WRENCH 24 3 7-14-90 S4 CLARK 20 LONDON P5 CLAMP 22 7 8-20-90 S5 ADAMS 30 ATHENS P5 CLAMP 22 5 8-11-91
Functional dependencies SNUM –> SNAME, STATUS,CITY CITY –> STATUS PNUM –> PNAME, WT SNUM,PNUM,SHIPDATE –> QTY
Attribute sizes (bytes) SNUM 5 PNAME 10 SNAME 20 WT 5 STATUS 2 QTY 5 CITY 10 SHIPDATE 8 PNUM 8 Total size 73
Third Normal Form
TABLE PART (100 rows, 23 bytes/row => 2.3 KB) PNUM PNAME WT Functional dependencies P1 NUT 12 PNUM –> PNAME, WT P2 BOLT 17 P3 WRENCH 17 P4 WRENCH 24 P5 CLAMP 12 P6 LEVEL 19
TABLE SHIPMENT (100k rows, 26 bytes/row => 2.6 MB) S N U M PNUM QTY SHIPDATE Functional dependency S1 P1 3 1-4-90 SNUM, PNUM, SHIPDATE–> QTY S1 P2 2 2-17-90
37
S1 P3 6 11-5-89 S1 P4 2 6-30-90 S1 P5 1 8-12-91 S1 P6 5 4-21-91 S2 P1 3 5-3-90 S2 P2 4 12-31-90 S3 P3 4 3-25-91 S3 P5 2 3-27-91 S4 P2 2 10-31-89 S4 P4 3 7-14-90 S4 P5 7 8-20-90 S5 P5 5 8-11-91
NOT Third Normal Form
TABLE SUPPLIER (200 rows, 37 bytes/row => 7.4 KB) S N U M SNAME STATUS CITY Functional dependencies S1 SMITH 20 LONDON SNUM –> SNAME, STATUS, CITY S2 JONES 10 PARIS CITY –> STATUS S3 BLAKE 10 PARIS S4 CLARK 20 LONDON S5 ADAMS 30 ATHENS Decomposition of Table Supplier into two Third Normal Form (3NF) Tables
38
Third Normal Form
TABLE SUPPLIER_W/O_STATUS (200 rows, 35 bytes/row => 7 KB) S N U M SNAME CITY Functional dependency S1 SMITH LONDON SNUM –> SNAME, CITY S2 JONES PARIS S3 BLAKE PARIS S4 CLARK LONDON S5 ADAMS ATHENS
TABLE CITY_AND_STATUS (100 rows, 12 bytes/row => 1.2 KB) CITY STATUS Functional dependency LONDON 20 CITY –> STATUS PARIS 10 ATHENS 30
39
Relational tables predicted by the ER model, with no functional dependencies given, just those implied by the diagram. Table 1: emphistory (jobtitle, startdate , enddate, empid ) Table 2: employee ( empid , empname, phoneno, officeno, projno,deptno) Table 3: project ( projno , projname, startdate, enddate) Table 4: dept ( deptno , deptname, mgrid)
40
Example of Table Design and Normalization (3NF) from a collection of FDs and an ER diagram
Functional dependencies (FDs) given empid, startdate –> jobtitle, enddate empid –> empname, phoneno, officeno, projno, deptno phoneno –> officeno projno –> projname, startdate, enddate deptno –> deptname, mgrid mgrid –> deptno
In general, the FDs can be derived from 1. Explicit assertions given 2. ER diagram (implied by ER constructs) 3. Intuition (your experience with the problem data)
Table 1: empid, startdate –> jobtitle, enddate
This table has a composite key that must be separated from functional dependencies (FDs) that involve any individual component of this key (e.g. empno) on the left side.
Table 2 Let us start with the following set of FDs and then refine them, eliminating transitive dependencies within the same table.
Given: empid –> empname, phoneno, officeno, projno, deptno phoneno –> officeno
We need to eliminate the redundant right sides of the transitive dependencies (office_no) and put them into other tables. Thus we get:
Table 2a: empid –> empname, phoneno, projno, deptno Table 2b: phoneno –> officeno
Table 3: projno –> projname, startdate, enddate
Table 4: deptno –> deptname, mgrid mgrid –> deptno
41
Functional Dependency Inference rules (Armstrong’s Axioms)
1. Reflexivity If Y is a subset of the attributes of X, then X->Y. X = ABCD, Y = ABC => X->Y X->X trivial case
2. Augmentation If X->Y and Z is a subset of table R (i.e. Z is any set of attributes in R), then XZ -> YZ .
3. Transitivity If X->Y and Y->Z then X->Z.
4. Pseudo-transitivity If X->Y and YW->Z then XW->Z. (transitivity is a special case of pseudo-transitivity when W is null)
5 . U n i o n If X->Y and X->Z then X->YZ.
6. Decomposition If X->YZ then X->Y and X->Z.
Superkey Rule 1. Any FD involving all attributes of a table defines a super-key on the LHS of the FD.
Given: any FD containing all attributes in the table R(W,X,Y,Z), i.e. XY -> WZ. Proof: (1) XY -> WZ given (2) XY -> XY by the reflexivity axiom (3) XY -> XYWZ by the union axiom (4) XY uniquely determines every attribute in table R, as shown in (3) (5) XY uniquely defines table R, by the definition of a table as having no duplicate rows (6) XY is therefore a super-key, by the definition of a super-key.
Super-key Rule 2. Any attribute that functionally determines a Super-key of a table, is also a super-key for that table.
Given: Attribute A is a super-key for table R(A,B,C,D,E), and E -> A. Proof: (1) Attribute A uniquely defines each row in table R, by the def. of a super-key (2) A -> ABCDE by the definition of a super-key and a relational table (3) E -> A given (4) E -> ABCDE by the transitivity axiom (5) E is a super-key for table R, by the definition of a super-key.
42
3NF Synthesis Algorithm (Bernstein)
Basic definitions g e H set of FDs
H+ closure of H – set of all FDs derivable from H using all the FD inference rules
H’ cover of H – any set of FDs from which every FD in H+ can be derived
H’(non-redundant) – non-redundant cover of H, i.e. a cover which contains no proper subset which is also a cover. Can be determined with quadratic complexity O(n2).
Example Given a set of FDs H, determine a minimal set of tables in 3NF, while preserving all FDs and maintaining only lossless decomposition/joins. H: AB->C DM->NP D->KL
A->DEFG D->M E->G L->D F->DJ PR->S G->DI PQR->ST
Step 1: Eliminate any extraneous attributes in the left hand sides of the FDs. We want to reduce the left hand sides of as many FDs as possible. In general:
XY->Z and X->Z => Y is extraneous (Reduction Rule 1) XYZ->W and X->Y => Y is extraneous (Reduction Rule 2)
For this example we mix left side reduction with the union and decomposition axioms: DM->NP => D->NP => D -> MNP D->M D->M
PQR->ST => PQR->S, PQR->T => PQR->.T PR->S PR->S PR->S
Step 2: Find a non-redundant cover H’ of H, i.e. eliminate any FD derivable from others in H using the inference rules (most frequently the transitivity axiom).
A->E->G => eliminate A->G from the cover A->F->D => eliminate A->D from the cover
Step 3: Partition H’ into tables such that all FDs with the same left side are in one table, thus eliminating any non-fully functional FDs. (Note: creating tables at this point would be a feasible solution for 3NF, but not necessarily minimal.)
R1: AB->C R4: G->DI R7: L->D R2: A->EF R5: F->DJ R8: PQR->T R3: E->G R6: D->KLMNP R9: PR->S
43
Step 4: Merge equivalent keys, i.e. merge tables where all FD’s satisfy 3NF.
4.1 Write out the closure of all LHS attributes resulting from Step 3, based on transitivities.
4.2 Using the closures, find tables that are subsets of other groups and try to merge them. Use Rule 1 and Rule 2 to establish if the merge will result in FDs with super-keys on the LHS. If not, try using the axioms to modify the FDs to fit the definition of super-keys.
4.3 After the subsets are exhausted, look for any overlaps among tables and apply Rules 1 and 2 (and the axioms) again.
In this example, note that R7 (L->D) has a subset of the attributes of R6 (D->KLMNP). Therefore we merge to a single table with FDs D->KLMNP, L->D because it satisfies 3NF: D is a super-key by Rule 1 and L is a super-key by Rule 2.
Final 3NF (and BCNF) table attributes, FDs, and candidate keys: R1: ABC (AB->C with key AB) R5: DFJ (F->DJ with key F) R2: AEF (A->EF with key A) R6: DKLMNP (D->KLMNP, L->D, w/keys D, L) R3: EG (E->G with key E) R7: PQRT (PQR->T with key PQR) R4: DGI (G->DI with key G) R8: PRS (PR->S with key PR)
Step 4a. Check to see whether all tables are also BCNF. For any table that is not BCNF, add the appropriate partially redundant table to eliminate the delete anomaly.
44
Maier’s Example using 3NF Synthesis [Maier,D. The Theory of Relational Databases, Computer Science Press, 1983] R = {A,B,C,D,E,F,G,H,I,J,K } Functional dependencies (FDs): (1) E –> A B C D F G H I J K (7) H I –> J (2) A B C –> E D F G H I J K (8) I J –> H (3) A B D –> E C F G H I J K (9) H J –> I (4) G –> H I J (5) C F –> K (6) D F –> K
Step 1 – No reduction of determinants necessary. Step 2 – Find non-redundant cover.
(4) G->HIJ => eliminate HIJ from (1), (2), and (3) (7) HI->J => reduce (4) to G->HI, eliminating J from (4) (5) CF -> K => eliminate K from (1) and (3) (6) DF->K => eliminate K from (2) (1) E->DFG => eliminate DFG from (2) (1) E->CFG => eliminate CFG from (3)
Step 3 – Partition into groups with the same left side. G1: E->ABCDFG G6: DF->K G2: ABC->E G7: HI->J G3: ABD->E G8: IJ->H G4: G->HI G9: HJ->I G5: CF->K
Step 4 – Merge equivalent keys, forming new groups. Construct final set of tables, attributes, FDs, and candidate keys.
R1: ABCDEFG ( E->ABCDFG, ABC->E, ABD->E with keys E, ABC, ABD) R2: GHI (G->HI with key G) R3: CFK (CF->K with key CF) R4: DFK (DF->K with key DF R5: HIJ (HI->J, IJ->H, HJ->I with keys HI, IJ, HJ)
45
Example of a 3NF table that is not BCNF, i.e. it has further anomalies: S = student, C = course, I = instructor SC -> I For each course, each student is taught by only one instructor. A cours
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.