100 likes | 199 Vues
Learn to analyze problem domains, build ER models, design tables, and run queries in this comprehensive seminar on relational database development. Gain insights into normalization and guidelines for efficient table design.
E N D
Seminar:Introduction to relational databases Development process: Analyse the problem domain and build a conceptual model (ER model) Table design Domains Primary key, foreign keys, constraints Quality checking design (normalisation) Create tables Run queries Introduction to the database field: The development process FEN 2012-09-03
Development Process Analyse the problem domain: Identify: • entities • relations FEN 2012-09-03
Development Process Build the conceptual model: • Attributes • Primary keys • Cardinality • Participation FEN 2012-09-03
Development Process Transform the ER model into relational schemas (tables): • 7 steps algorithm: • Entity -> Table(composite, and multi value attributes) • Weak entity -> Table(composite key, include owners key) • 1-1 and 1-n relations(include primary key from one side as foreign key on the other side) • n-m relations(new table with the primary keys from both sides as foreign keys, composite primary key) • Multi value attributes - > new table(include the owners primary key as foreign key) • Relations with degree > 2 -> new table(include all participants primary keys as foreign keys, composite primary key) FEN 2012-09-03
Development Process Result: FEN 2012-09-03
Development Process • Table Semantics • Avoid Redundant Information • Minimise NULL-values • Disallowing the generation of spurious tuples when joining tables. Informal Design Guidelines: Normalisation: • 1NF • 2NF • 3NF • BCNF • Look for functional dependencies / determinants that are not keys Not in 2NF! Not in 3NF! FEN 2012-09-03
Development Process Guideline for Normalisation All attributes are to depend on the key, the whole key, and nothing but the key. So help me Codd. And remember: • FDs are business rules • Normalisation fights redundancy and other maladies in table design FEN 2012-09-03
Development Process Table definitions: • Data types (domains) • NULLsallowed? • Primary key (ids?) • Referential constraints (FK-PK references) Constraint FEN 2012-09-03
Development Process Execute queries: • SELECT, INSERT, UPDATE, DELETE • SELECT: • Rows and columns • Joins • Set operations FEN 2012-09-03
The End! Thank you very much – hope you liked it. (fen@ucn.dk, http://public.ucn.dk/fen/Angers) FEN 2012-09-03