Database design and queries

[rockyrules]

Hi dear all, I am new to database design and database structure, In my internal test i faced one database design problem, tried to solve this but i am still having doubts so please experts give me clear solution for this Problem is Media bank is a web application for storing and retrieval of Media files. In Media bank, files are stored in folders (just like in the case of windows). A folder can have sub-folders to any level of depth. A file in a folder can exist either in published or un-published state. Every folder will have the following information associated with it: folder name and folder description. Every file will have the following information associated with: file name, file description, physical location in server, size in kbs, type of file. 1. Write a normalized database design for the above mentioned scenario. Also show primary keys, foreign keys, and also what kind of relationship (1-M , M-1 or M-M) exists between them. 2. We add a new feature to Media bank: A file can contain different versions, but only one version will be active at a time. Any version of a file can be set to active by the administrative user. Modify the database deign above to incorporate this new feature. 3. Write SQL Query to retrieve a. All files of type ‘images’ with the following information: File name, File description, state of file (unpublished/published), Folder name, Folder description. b. All folders with following information: Folder name, Number of files in published state in that folder c. All files with following information File name, complete folder path (all parents until root folder) Thanks in advance

[Mycroft Holmes]

Please leave fill out the following to get a valid response. Educational Institution: Course Enrolled: Instructor Name: Contact Number: