1 / 21

Data Modelling

Data Modelling. EAR model. This modelling language allows a very small vocabulary: Just as English has nouns, verbs, adjectives, pronouns.. , EAR models have only 3 central concepts Attribute – a simple, atomic data item like a date, a name, a number [also called Property or Field]

morwen
Download Presentation

Data Modelling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Modelling

  2. EAR model • This modelling language allows a very small vocabulary: Just as English has nouns, verbs, adjectives, pronouns.. , EAR models have only 3 central concepts • Attribute – a simple, atomic data item like a date, a name, a number [also called Property or Field] • Entity – a group of attributes which are all related to the same thing [also called Entity Type or Class] • Relationship – a relationship between Entities.

  3. MMS Photo : EAR diagram Entity Each Photo created by exactly one Member Relationship Attribute Each Member has created many Photos Primary key Data Type Diagram generated by QSEE

  4. Case Tool - QSEE • is a free download for individual use • is available in the lab • enables a wide range of diagram types to be created • Entity-relationship diagram • With generation of SQL for creating a database (with some fixes )

  5. Entity (type) • Typically represents a type of thing – a person, department, photo • Like a Class in Object-Orientation • Has a name • Name becomes the name of the table in a database • I prefer a singular noun – Employee, Department. The table will contain a varying number of entity instances but the Entity Type is singular • Has attributes or properties (which become fields or columns in a database) • Entity instances are related (linked) to other entity instances – relationships • Has an Annotation (in QSEE) • Text to describe the scope of this type (what things can be one of these), the meaning and usage, a typical example.

  6. Attribute • Name • Must be unique across the Entity • Less confusion if unique across the database • Avoid SQL reserved words • Type (data type) • SQL provides a set of available data types – essential ones are: • integer – a whole number • numeric – number with decimal part • character – fixed length string • varchar – variable length string • date – hold dates like 2005/10/14 • blob – a very large field

  7. Enumeration • A restricted set of values • e.g. the valid images types to be stored in the photo album • First define the type • Then it can be used as a data type for an attribute.

  8. Other attribute ‘attributes’ in QSEE • Key • Tick if this attribute forms part of the primary key – the fields which together uniquely identify each entity instance in the table • Null • Tick if this attribute may be left undefined • Unique • Tick if each entity instance must have a unique value in the table • Auto-increment • [Cannot set in QSEE at present] • When adding a new record, this attribute will be given the next value in sequence • Annotation • Descriptive text – what the attribute represents, legal values, example values…

  9. Relationship CARDINALITY M1 M2 M4 P1 P4 P5 P3 P7 One-one One-many member photo Many-many

  10. It is hard to get this notation the right way round. • Always read it both ways • Every X is related to how many Y’s? • Every Y is related to how many X’s?

  11. Relationship OPTIONALITY M1 M3 M2 P1 P4 P5 P7 P3 P7 One-many member photo

  12. Relationships in QSEE • Name • Names the relationship • There can be multiple relationships between the same entity types e.g, • Employee –Department • Works in • Is manager of • Has worked in • Must never work in • Relationship ends (roles) can be named too • Cardinality • from source (start) to destination (end) • Optional • Identifier • For 1-M, does the source (partly) identify the destination • Annotation • Text describing the meaning of this relationship

  13. Model to target implementation • QSEE will produce 3 types of schema • XML document • DTD • XML schema • SQL Relational Database • ORACLE • MySQL • Microsoft SQL server • SQL 2 standard

  14. Relational Database target • Each entity becomes a table • Each attribute becomes a column of a table • Each relationship become common fields and values in two tables. • Two cases • One-many • Many-many

  15. One-many relationship • Each one-many relationship is implemented by adding the primary key of the entity on the one side (the parent) as new columns on the many (the child) side • So Image gets • a new column called ‘blogid’ • a foreign key constraint defines the relationship • When an image is added to the database, the appropriate value of blogid for the creator is added to the image record (a common value)

  16. CREATE TABLE Blogger( blogid INTEGER NOT NULL, name VARCHAR(20), mobilePhoneNo CHAR(15) UNIQUE, CONSTRAINT pk_Blogger PRIMARY KEY (blogid) ) TYPE=INNODB; CREATE TABLE Image( imageid INTEGER NOT NULL, imageBody MEDIUMBLOB, title VARCHAR(100), blogid INTEGER NOT NULL, CONSTRAINT pk_Image PRIMARY KEY (imageid) ) TYPE=INNODB; ALTER TABLE Image ADD INDEX (blogid), ADD CONSTRAINT fk1_Image_to_Blogger FOREIGN KEY(blogid) REFERENCES Blogger(blogid) ON DELETE RESTRICT ON UPDATE RESTRICT;

  17. Relationship in RDBMS M1 M3 M2 P1 M1 P4 M1 P5 M1 P7 null P3 M2 P7 M2

  18. Using a foreign key • To find all the Images belonging to blogger M2 Select * from Image where blogid=‘M2’ • To find the creator of photo P1 Select * from image natural join blogger Where imageid=‘P1’

  19. Target is XML

  20. Target XML • Here the names of columns and entities are included in the data. • The relationship between blogger and images is containment – all the images for a blogger occur immediately after the details of the blogger herself • Same ER model • Different implementations in different technologies.

More Related